Goto Next VISIBLE cell below

  • Thread starter Thread starter Rasmus
  • Start date Start date
R

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
 
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
 
Rasmus said:
Current activecell is A13 and I'm using autofilter. What the VBA
command to go to the next VISIBLE cell below - i.e. A21 ?
Click to expand...

Hi, Rasmus.

Try this

......
ActiveCell.Offset(1, 0).Activate
Do While ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Activate
Loop
......
 
Dim rng as Range
set rng = Range("A13")
do
set rng = rng.offset(1,0)
Loop while rng.entireRow.Hidden = True
rng.Select
 
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
 
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
 
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
 
cucchiaino said:
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
......
Click to expand...
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?
 
cucchiaino said:
Hi, Rasmus.

Try this

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


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.
 
Back
Top