CODEWITHSUNDEEP
excelvbafilterrange
timetravelprimer
timetravelprimer

Reputation: 111

How to select a range after filtering?

I'm trying to select a range, which contains first 16 visible rows, after filtering.

Without applied filter it works ok.

Range("A1").AutoFilter
    ActiveSheet.ListObjects("Empty_Locations").Range.AutoFilter Field:=3, Criteria1:=BrandSelection
    ActiveSheet.ListObjects("Empty_Locations").Range.AutoFilter Field:=4, Criteria1:="<>Printed", Criteria2:="<>Occupied"

        With ActiveSheet.Range("A1")
        With .Offset(1, 0).Resize(Rows.Count - .Row, 1)
            .SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
        End With
        End With

Dim SelectedCell16 As Range
Set SelectedCell16 = Selection.Offset(15, 3).SpecialCells(xlCellTypeVisible)

ActiveSheet.Range(Selection, SelectedCell16).Select

I know that SpecialCells(xlCellTypeVisible) method is somehow involved but I can't get past it. Selection is totally wrong.

Help would be appreciated.

Upvotes: 0

Views: 751

Answers (1)

Kirill Tkachenko
Kirill Tkachenko

Reputation: 426

Unfortunately, using .Offset will not work in this case, because it returns whatever range is 15 rows below your selection, whether its visible or hidden. However, Range.SpecialCells(xlCellTypeVisible) will have several .Areas, and each Area only includes visible cells. You can loop through the .Areasuntil you get at least 16 rows and select them, like shown below:

Sub test()
    Dim area As Range
    Dim CellCount As Integer
    Dim firstCell As Range
    Dim lastCell As Range

    With ActiveSheet.Range("A1").Offset(1, 0).Resize(Rows.Count - ActiveSheet.Range("A1").Row, 1)

        'first cell will be the the first cell of Areas(1)
        Set firstCell = .SpecialCells(xlCellTypeVisible).Areas(1).Cells(1, 1)

        'Get last cell by looping through areas until their total cell count reaches 16.
        For Each area In .SpecialCells(xlCellTypeVisible).Areas
            'first area may already contain more than 16 cells, in which case we just get its 16th cell and exit.
            'If this is not the case, we add up rows.Count of each area until we get more than 16, and when that happens,
            'we get the cell of last area which is needed to get to 16.
            If CellCount + area.Rows.Count >= 16 Then
                Set lastCell = area.Cells(16 - CellCount, 1)
                Exit For
            End If
            CellCount = CellCount + area.Rows.Count
        Next
    End With

    'finally, from the firstCell and lastCell we can get the range of first 16 visible cells.
    ActiveSheet.Range(firstCell, lastCell).Select
End Sub

Upvotes: 1

Related Questions