How do I simulate a cursor down in Excel Macros

[charlbury]

I don't want to selec the next cell down, I want simulate a cursor
down.

For example, if I have data grouped or hidden rows, when I do a cursor
down i don;t want it ogo to the current row + 1, I want it to go to the
next visible row which might be current row +Y.

Hitting cursor down does this but how do I make that happein in a macro

Thanks

Steve

 

[damorrison]

Sub YSelect ()
Range("Y1").Select
End Sub
Assign this to a button or picture

Now this is what you asked for, but I don't think it's what you want
You may want a worksheet event, lets see how the ball rolls today with
other replies

 

[Nigel]

This will select the next cell down from current row which is not hidden.
If checks just in case you are on last row of worksheet!

Sub NextVisibleRow1()
Dim xr As Long
xr = ActiveCell.Row + 1
If xr <= Rows.Count Then
Do While Rows(xr).EntireRow.Hidden = True
xr = xr + 1
Loop
Cells(xr, ActiveCell.Column).Select
End If
End Sub

 

[charlbury]

Yeah, I thought about putting it in a loop and checking the hidden
property but I thought, surely, there must be a CursorDown function
that would achieve the same thing and do all necessary checking for
more rows etc. etc.

Does anyone know if there is, or does anyone know if there definately
isn't!

Thanks for the replies.

Steve

 

[Tom Ogilvy]

ActiveCell.End(xldown)

 

[Tushar Mehta]

Tom Ogilvy has already posted a response to your specific question.
You can discover the same yourself with the XL macro recorder (Tools |
Macro > Record new macro...)

Also, notice Tom simply used End(xlDown) without any follow up
'Select'. There is rarely a need to actually activate/select an XL
object. For an intro see
Beyond Excel's recorder
http://www.tushar-
mehta.com/excel/vba/beyond_the_macro_recorder/index.htm

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Nigel]

Tom,

Does not that take you to the last row in the region ? The OP wanted the
cell of the next un-hidden row.

 

[Tom Ogilvy]

I was interpreting the cursordown as end, then down arrow, but I see your
point - he probably wants just a down arrow. I think he would need sendkeys
for that.

or something like

set rng = ActiveCell.offset(1).Resize(1000).SpecialCells(xlVisible)
rng(1).Select

The 1000 being somewhat arbitrary

 

[Tom Ogilvy]

Or a bit better:

Sub EFG()
Dim rng As Range
Set rng = Range(ActiveCell.Offset(1), _
ActiveCell.Offset(1).End(xlDown)) _
.SpecialCells(xlVisible)
rng(1).Select
End Sub

 

[Nigel]

Very neat !!

--
Cheers
Nigel

Or a bit better:

Sub EFG()
Dim rng As Range
Set rng = Range(ActiveCell.Offset(1), _
ActiveCell.Offset(1).End(xlDown)) _
.SpecialCells(xlVisible)
rng(1).Select
End Sub

 

[damorrison]

what are you really trying to do?? Obviously your not asking for the
right thing!