asked on

VB filter

Is there a way to set the filter in column J to "Top 10" via a command button

I was hoping to set the filter in B by:-

Private Sub CommandButton1_Click()
ActiveSheet.[B1].Select
Sheets("Archive").Unprotect Sheets("DataSheet").Range("B3").Value
Dim wks As Worksheet
Dim currentFiltRange As String
Dim cDateCriteria() As Variant
Dim i As Long
Dim r As Range
Dim rng As Range

Set wks = ThisWorkbook.Sheets("Archive")

If wks.FilterMode Then wks.ShowAllData

wks.Range("$C$9:$C$60000").AutoFilter Field:=2, _
Criteria1:=">=" & Format(wks.Range("C2").Value, "mm/dd/yyyy"), _
Operator:=xlAnd, _
Criteria2:="<" & Format(wks.Range("C3").Value + 1, "mm/dd/yyyy")
Sheets("Archive").Protect Sheets("DataSheet").Range("B3").Value, AllowFiltering:=True

End Sub

Then at the end of the code I would add the filter by top 10 in coumn J
help19.xls

sbaughan

Can't you just set a formula in column K that holds in K1 for example "=J1", extend it down 10 lines and filter all but blanks ?
... been looking for the solution you ask and it seems that you'll need a workaround like this one ... or somebody esle has a better idea?
You can hide the column K if it bothers you

Kiwi-123

ASKER

The function should already exist on the filter bar, I was just looking for the vb to make this a command click.

So I can already list the top 10 manually, but was looking for a lazy way to set the autofilter

ASKER CERTIFIED SOLUTION

sbaughan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kiwi-123

ASKER

Thanks sbaughan, would you be able to help me integrate this into the existing code?

sbaughan

Sorry, I didn't see your post.
I'll try something tomorrow if you didnt't already make it.
Tell us how it goes!