Macros - Relative cells in Excel

[Guest]

How can you program a macro to select data to the bottom of a list rather
than to a specific cell number? (i.e. for data of varying length)

 

[Nikos Yannacopoulos]

The trick is to selectthe cell at the top of the list, which is known, and
then tell Excel to go all the way down to the last cell before the first
empty one. So, assuming the list starts in D4, the code would be:

Range("D4").Select
Selection.End(xlDown).Select

HTH,
Nikos

 

[Guest]

Hi Nikos
That was really useful, thank you. Although I'm now having trouble telling
the macro to move to the cell below the last row of data. So I've got 3 pages
of data that I want to copy and paste into one long list in a separate
workbook. If you use the visual basic command:

Range("D4").Select
Selection.End(xlDown).Select

to move to the end of the data, the row in question contains data that will
be written over if the next set of data is pasted, how do you command the
macro to move down a cell without referring it to a specific cell (e.g. D158)?
Tom

 

[Chip Pearson]

Try something like

Selection.End(xlDown)(2,1).Select


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

 

[Guest]

Chip, you're a legend - works a treat. Thanks for helping

Tom