CODEWITHSUNDEEP
excelvba
user2030857
user2030857

Reputation: 9

Filtering by a Value with a button

I would like to create a button that will look for a value in a particular column and filter by it.

I have 7 columns with data and on the top row i would like to create something like a search bar/ column filtering button. The idea is enter a value in Cell B2 and click a button to have the column G sorted by the value in B2.

Is this possible?

Upvotes: 0

Views: 418

Answers (1)

Floris
Floris

Reputation: 46375

If I understand correctly that by "sort" you mean "filter", then you can do the following.

Create a button on your sheet (Developer tab on the ribbon, insert... button),

enter image description here

and when prompted, add the following code to the Button_Click() Sub :

Private Sub CommandButton1_Click() 
  [G:G].AutoFilter Field:=7, Criteria1:=Range("b2").Value 
End Sub

Be careful that you don't put your button on a row that might get "filtered" or it will disappear. Also, if your "sort on this value" field is in row 2, it might disappear as well.

I'm sure you can figure out how to deal with those things.

Alternative interpretation of question

If instead you are looking to sort on a particular column, whose name is given in cell B2, then your code might look something like this:

Option Compare Text
Sub Button1_Click()
Dim sortCol As Integer
Dim headerRange As Range
Dim sortRange as Range

' assuming data to be sorted is in columns E through K
set headerRange = [E1:K1] ' or wherever the headers are
sortCol = WorksheetFunction.Match([B2].Value, headerRange)
' this line for debug:
set sortRange = [E:E].Offset(, sortCol - 1)
MsgBox "you will sort by column " & sortRange.Address

With ActiveSheet
    .Range("E:K").Select
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=sortRange, _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal
    With .Sort
    .SetRange Range("E:K")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
End With

End Sub

Upvotes: 2

Related Questions