Search code examples
excelvba

Excel VBA - Set Range Filtered table Visible Cells

I am using this code to set a range of filtered table to visible cells only.

Set rngMyRange = Selection.SpecialCells(xlCellTypeVisible)

However, there is a strange bug if only one cell is selected, that is, the code selects all used range in the filtered table.

If the selection is greater than one cell, then the code works as expected. Is there a way around this?

Solution

  • Usually using "Selection" is not a good practice; I am guessing you just need to get the range of the visible cells for that you can easily use this:

    Sub GetVisibleRangeOnly()
    Dim tbl As ListObject
    Dim rng As Range

    'change the name of the table and worksheet as you need
    Set tbl = Worksheets("Sheet1").ListObjects("Table1")

    'Note: if there is no visible cell after filtraton rng IS NOTHING will be TRUE
    Set rng = tbl.DataBodyRange.SpecialCells(xlCellTypeVisible)

End Sub