Select header and first visible row

[nora1212]

Hi,

I'm trying to select the header of a filtered list and the first row of the filtered list. The equivalent of selecting say A1:J1 manually (with shift) and then moving down one row to select both the header range and the first visible row range. I know it involves the use of "special cells" and "visible cells" but can't quite make anything work?

Thanks for your help

 

[Akuini]

Let's say the filtered range is "A1:J100":

Dim c As Range
With Range("A1:J100")
    Set c = Union(.Rows(1), .Offset(1).SpecialCells(xlCellTypeVisible).Rows(1))
End With
Debug.Print c.Address

 

[nora1212]

Hi. Thanks for the response but this didn't appear to select any rows. I'd like the header row to be selected, and the next filtered row selected, similar to what i could achieve manually so i can copy and paste elsewhere or print.

Let's say the filtered range is "A1:J100":

Dim c As Range
With Range("A1:J100")
    Set c = Union(.Rows(1), .Offset(1).SpecialCells(xlCellTypeVisible).Rows(1))
End With
Debug.Print c.Address

 

[Akuini]

Just change :

Debug.Print c.Address

to:

c.Select

 

[nora1212]

Just change :

Debug.Print c.Address

to:

c.Select

Thanks Akuini. Spot on!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.