SpecialCells(xlCellTypeVisible) extra row is selected in excel vba

Question

Hello All,

Currently working on SpecialCells(xlCellTypeVisible) where after filtering data, the cells would get highlighted with green color as shown in image 1, but when the formula is used Row no is also getting selected.

The result which I need to get visible cells would highlighted with no extra row added as shown in image 2.

Below is formula used.

OB2.ActiveSheet.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows.Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 0.399975585192419
            .PatternTintAndShade = 0
        End With

Don't use `UsedRange`; [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Apr 22 '20 at 18:06
If you do use `UsedRange` (but don't), you can use `Resize` to resize to `UsedRange.Rows.Count - 1`. — BigBen, Apr 22 '20 at 18:22

score 1 · Accepted Answer · answered Apr 22 '20 at 18:26

We try to remove the header by using .Offset(1,0). This however adds a row to the bottom. Consider:

Sub RemoveHeaderRow()
    Dim tablee As Range
    Dim tableeBody As Range

    Set tablee = Range("A1").CurrentRegion
    Set tableeBody = tablee.Offset(1, 0).Resize(tablee.Rows.Count - 1, tablee.Columns.Count)

    tableeBody.Select
End Sub

This is the range to use SpecialCells on.

SpecialCells(xlCellTypeVisible) extra row is selected in excel vba

1 Answers1