Cell selection with hidden rows

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

I have a spreadsheet that has hidden rows (hidden by using AutoFilter).

I wish to write a macro in which I need to select a cell on the visible row
below the current cell. For example if the currently selected cell is on
row 2 and rows 3-6 are hidden I wish to select the cell on row 7.

If the rows were not hidden I could use
ActiveCell.Offset(1,0).Range("A1").Select

How can I achieve what I want with an unknown variable number of hidden rows
between two visible rows?
 
Use RowHeight property of range object as follows:

****************code starts*************

Sub GoToNextVisibleCellDown()
Dim cell As Range
Set cell = ActiveCell
Do
Set cell = cell.Offset(1, 0)
Loop While cell.RowHeight = 0
cell.Select
Set cell = Nothing
End Sub

****************code ends*************
 
DomThePom said:
Use RowHeight property of range object as follows:

****************code starts*************

Sub GoToNextVisibleCellDown()
Dim cell As Range
Set cell = ActiveCell
Do
Set cell = cell.Offset(1, 0)
Loop While cell.RowHeight = 0
cell.Select
Set cell = Nothing
End Sub

****************code ends*************
Click to expand...

Excellent, thank you. I'm a bit of a novice as you might have guessed!
 
Back
Top