VB filter

Posted on 2012-09-20
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()
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
Question by:Kiwi-123
    LVL 5

    Expert Comment

    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

    Author Comment

    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
    LVL 5

    Accepted Solution

    ActiveSheet.Range("Your_Range").AutoFilter Field:=1, Criteria1:="10", _

    Author Comment

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

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now