How to obtain the Row number of next visible cell Down?

[EagleOne]

2007

VBA answer please: How to obtain the Row number of next Visible cell Down?

Of course Offset(1,0) returns the next actual cell not Visible cell?'

This s/b easy but ....... it is a Da! moment

TIA EagleOne

 

[Ken]

E1

Sub test()
i = ActiveCell.Row
Do While Cells(i + 1, 1).EntireRow.Hidden = True
i = i + 1
Loop
MsgBox i + 1
End Sub


Good luck.

Ken
Norfolk, Va

 

[meh2030]

2007

VBA answer please: How to obtain the Row number of next Visible cell Down?

Of course Offset(1,0) returns the next actual cell not Visible cell?'

This s/b easy but ....... it is a Da! moment

TIA EagleOne

You could use a Do Loop or a .End(xlDown). Keep in mind though that
you will need to account for the last instance of data because
ultimately row 65536 is the end.

Do Until ActiveCell.Value <> ""
'or you might need a IsEmpty(ActiveCell.Value)
'or you could use IsNumeric
'or some other function....

ActiveCell.Offset(1,0).Select

If ActiveCell.Row = 65536 Then
MsgBox "You reached the end of the spreadsheet."
Exit Sub
End If

If ActiveCell.Value <> "" Then
rowNum = ActiveCell.Row
exit do
End If
Loop

OR

rowNum = ActiveCell.End(xlDown).Row

I hope this helps

Matt

 

[EagleOne]

Thanks for the help.

Realizing that the visible "range" is an array I thought that there may be a way to address without
using a loop.

I tried an End(xlDown) but it took me from the activeCell to the bottom (65536) even though there
were interviening hidden rows

EagleOne

 

[Don Guillett]

try this idea

Sub nextvisrow()
Set myrng = Range(Cells(ActiveCell.Row+1, ActiveCell.Column),
Cells(rows.count,activecell.Column))
myrng.SpecialCells(xlCellTypeVisible).Cells(1).Select
End Sub

 

[EagleOne]

Thanks Don

 

[Don Guillett]

Glad to help.

 

[EagleOne]

Yep! Your approach is smart and very efficient.