How to select Visible Cells range minus 2 rows (frozen panes)

[LuisE]

I'm trying to programatically select the visible cells in the current region
except the rows 1 and 2 at the top of the sheet.

Thanks in advance.

 

[ward376]

This will select the current region of the first cell minus two rows:

Cells(1, 1).CurrentRegion.Offset(2, 0).Resize(Cells(1, 1). _
CurrentRegion.Rows.Count - 2, Cells(1,
1).CurrentRegion.Columns.Count).select

Cliff Edwards

 

[ward376]

Or to actually answer your question:

Cells(1, 1).CurrentRegion.Offset(2, 0). _
Resize(Cells(1, 1).CurrentRegion.Rows.Count - 2, _
Cells(1, 1).CurrentRegion.Columns.Count). _
SpecialCells(xlCellTypeVisible).Select

will select only the visible cells.

Cliff Edwards

 

[LuisE]

Thanks a lot

 

[LuisE]

How would it be to exclude the last visible row as well?

 

[ward376]

To leave the last row visible:

Cells(1, 1).CurrentRegion.Offset(2, 0). _
Resize(Cells(1, 1).CurrentRegion.Rows.Count - 3, _
Cells(1, 1).CurrentRegion.Columns.Count). _
SpecialCells(xlCellTypeVisible).Select

'explanation: _
..Offset(2, 0) (moves range down two rows) _
_
Resize(Cells(1, 1).CurrentRegion.Rows.Count - 3 _
(tells it the range is 3 rows less than the total number of rows)

Cliff Edwards