Goto Next VISIBLE cell below

[Rasmus]

Current activecell is A13 and I'm using autofilter. What the VBA command to
go to the next VISIBLE cell below - i.e. A21 ?

Regards,
Rasmus

 

[Chip Pearson]

Rasmus,

I think you'd need to loop downwards, checking the Hidden
property of the row.

Dim Rng As Range
Set Rng = ActiveCell
Do
Set Rng = Rng(2, 1)
Loop Until Rng.EntireRow.Hidden = False


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[cucchiaino]

Current activecell is A13 and I'm using autofilter. What the VBA
command to go to the next VISIBLE cell below - i.e. A21 ?

Hi, Rasmus.

Try this

......
ActiveCell.Offset(1, 0).Activate
Do While ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Activate
Loop
......

 

[Tom Ogilvy]

Dim rng as Range
set rng = Range("A13")
do
set rng = rng.offset(1,0)
Loop while rng.entireRow.Hidden = True
rng.Select

 

[Soo Cheon Jheong]

Try this:

Dim rng As Range
Dim i As Long
For Each rng In Range(ActiveCell, Cells(Rows.Count,
ActiveCell.Column)).SpecialCells(Type:=12, Value:=23)
If i > 0 Then
rng.Select
Exit For
End If
i = i + 1
Next

 

[Tom Ogilvy]

why not just

Range(ActiveCell.Offset(1,0), _
Cells(Rows.Count,ActiveCell.Column)). _
SpecialCells(Type:=12, Value:=23)(1).Select

Add error checking of course

 

[Greg Glynn]

Sub JumpToNextVisibleColumn()

Dim Rng As Range
Set Rng = ActiveCell.Offset(0, 1)

While Rng.EntireColumn.Hidden = True
Set Rng = Rng.Offset(0, 1)
Wend

Rng.Activate

End Sub


Sub JumpToNextVisibleRow()

Dim Rng As Range
Set Rng = ActiveCell.Offset(1, 0)

While Rng.EntireRow.Hidden = True
Set Rng = Rng.Offset(1, 0)
Wend

Rng.Activate

End Sub

 

[sujit dhar]

Rasmus wrote:
> Current activecell is A13 and I'm using autofilter. What the VBA
> command to go to the next VISIBLE cell below - i.e. A21 ?
>

Hi, Rasmus.

Try this

......
ActiveCell.Offset(1, 0).Activate
Do While ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Activate
Loop
......

Hi Sir,
I was apply above code in my userform, it was worked well at small database. but I have faced a issue that I have a large database with rows around 75000 approx, so when i applied that loop at visible cell, my excel was going to crashed (ie. loop from row 100 to row 15000 ) excel did not respond. is their any solution?

 

[oazzam]

Hi, Rasmus.

Try this

......
ActiveCell.Offset(1, 0).Activate
Do While ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Activate
Loop
......

Hi, I know that my comment is only 18 years after your comment, I just hope you are doing fine and you're well, because your comment helped me the most.

I signed up on this website just to thank you.