Filter option

[ashani]

Hi
I'm new to VBA and looking for some guidance. I'm looking to have a drop down list with 2 options i.e. Text1 & Text2. The data in column D either has Text1 or Text2 - what I'm looking for that when the user select from that option Text 1 or Text 2 then all rows filtered and shows the selected item only.

I don't want to set normal filter as there are some coding in the background which may mess things up.

Please can someone guide me.

thank you

 

[Trebor76]

Hi ashani,

You can use this event macro on the sheet in question to hide the rows:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lngMyRow As Long, lngLastRow As Long
    Dim rngHide As Range
    Dim strMyValue As String
    Dim strMyValues As String
    
    strMyValue = "$A$1" 'Cell containing the drop down.  Change to suit but leave dollar signs.
    strMyValues = "B" 'Column containing the values each to be compared to the contents of 'strMyValue'. Change to suit.
    
    If Target.Address = strMyValue Then 'Assumes the drop-down is in cell A1. Change to suit.
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        ActiveSheet.Cells.EntireRow.Hidden = False
        lngLastRow = ActiveSheet.Cells(Rows.Count, strMyValues).End(xlUp).Row
        For lngMyRow = 2 To lngLastRow 'Assumes the list starts in Row 2. Change to suit.
            If Range(strMyValues & lngMyRow).Value <> Target.Value Then
                If rngHide Is Nothing Then
                    Set rngHide = Cells(lngMyRow, strMyValues)
                Else
                    Set rngHide = Union(rngHide, Cells(lngMyRow, strMyValues))
                End If
            End If
        Next lngMyRow
        'If the 'rngHide' range has been set, then...
        If Not rngHide Is Nothing Then
            '...hide the row(s) from in it
            rngHide.EntireRow.Hidden = True
        End If
        With Application
            .EnableEvents = True
            .EnableEvents = True
        End With
    End If

End Sub

Note an event macro needs to be put on the sheet it's meant to work with. To do this follow these four steps:

1. Copy the code from thread 2 to the clipboard (Ctrl + C)
2. Right click on the sheet name you want the code to run on and from the shortcut menu select View Code
3. Paste (Ctrl + V) the code from Step 1 into the blank white screen (Visual Basic Editor)
4. From the File menu select Close and Return to Microsoft Excel

Regards,

Robert