CODEWITHSUNDEEP
excelvba
ROn
ROn

Reputation: 139

SpecialCells(xlCellTypeVisible) extra row is selected in excel vba

image1 image2

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

Upvotes: 0

Views: 565

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

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

enter image description here

This is the range to use SpecialCells on.

Upvotes: 1

Related Questions