?
Solved

VB filter

Posted on 2012-09-20
5
Medium Priority
?
438 Views
Last Modified: 2012-10-25
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
0
Comment
Question by:Kiwi-123
  • 3
  • 2
5 Comments
 
LVL 5

Expert Comment

by:sbaughan
ID: 38419085
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
0
 

Author Comment

by:Kiwi-123
ID: 38419133
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
0
 
LVL 5

Accepted Solution

by:
sbaughan earned 2000 total points
ID: 38419211
ActiveSheet.Range("Your_Range").AutoFilter Field:=1, Criteria1:="10", _
        Operator:=xlTop10Items
0
 

Author Comment

by:Kiwi-123
ID: 38419283
Thanks sbaughan, would you be able to help me integrate this into the existing code?
0
 
LVL 5

Expert Comment

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question