How do I simulate a cursor down in Excel Macros

  • Thread starter Thread starter charlbury
  • Start date Start date
C

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
 
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
 
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
 
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 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
 
Tom,

Does not that take you to the last row in the region ? The OP wanted the
cell of the next un-hidden row.
 
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
 
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
 
Very neat !!

--
Cheers
Nigel



Tom Ogilvy said:
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
Click to expand...
 
Back
Top