Selecting a cell at the end of a list.

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I am recording a macro to move the active cell to the
first blank cell at the bottom of a column of values.
While recording the macro I am using the end down key to
move to the last record of the list and then the down key
to move to the first blank cell. However the macro is
recording the actual cell reference and not the down key.
Any ideas?

Thanks,
Steve
 
I am trying to select the cell directly below the last
Click to expand...
value in the list, and your line takes me to the last
value in the list. Can you help?
Thank's Steve


-----Original Message-----
 
It needs to move down a row

ActiveCell.End(xlDown).Offset(1,0).Select

If there is a chance of embedded blank cells, working up is more foolproof

Cells(Rows.Count,Activecell.Column).End(xlUp).Offset(1,0).Select

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Sorry, missed the last step:

ActiveCell.End(xlDown).Offset(1, 0).Select


realize that this has no error checking - if the activecell is the
last filled cell in the column, End(xlDown) will take you to row
65536, and .Offset(1, 0) will then throw an error.

To prevent that, you can look "up" from the bottom:

Cells(ActiveCell.Row, Rows.Count).End(xlUp).Offset(1, 0)

which will work as long as your list is <65536 items.

I am trying to select the cell directly below the last
Click to expand...
value in the list, and your line takes me to the last
value in the list. Can you help?
Thank's Steve


-----Original Message-----
one way:

ActiveCell.End(xlDown).Select
Click to expand...
[/QUOTE]
 
Mixed my directions:

Cells(Rows.Count,ActiveCell.Column).End(xlUp).Offset(1, 0)In
article
 
Hi JE!

Less spirit and more water is prescribed for this problem!

Seasons Greetings from sunny Australia where Sydney is forecast for +
30 C.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top