How To Select Filtered Rows In Excel Vba

In Excel VBA, you can select filtered rows using the “SpecialCells” method. The “SpecialCells” method allows you to work with specific types of cells, such as visible cells only in a filtered range.

Here’s an example:

Sub SelectFilteredRows()
    Dim rng As Range
    Dim filteredRange As Range
    
    ' Assuming you have a table in Range A1:C10
    Set rng = Range("A1:C10")
    
    ' Filter the table based on a criteria
    rng.AutoFilter Field:=1, Criteria1:="Apple"
    
    ' Select the filtered range
    On Error Resume Next
    Set filteredRange = rng.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    ' Check if any cells are selected
    If Not filteredRange Is Nothing Then
        ' Do something with the filtered range
        MsgBox "Filtered range selected!"
    Else
        MsgBox "No filtered rows found!"
    End If
    
    ' Clear the filter
    rng.AutoFilter
    
    ' Clear the selection
    rng.ClearContents
End Sub
    

In this example, we start by setting the range “rng” to the desired table range. Then, we filter the range based on a certain criteria (in this case, we filter the first column for the value “Apple”).

Next, we use the “SpecialCells” method with the “xlCellTypeVisible” parameter to select only the visible cells in the filtered range. We use error handling to avoid any runtime errors if no cells are visible.

You can then perform any action you want with the filtered range. In the example, we display a message box indicating whether a filtered range was selected or not.

Finally, we clear the filter using the “AutoFilter” method and clear the selection using the “ClearContents” method.