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)

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


"BeardT" wrote in message
...
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)

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


"Nikos Yannacopoulos" wrote:

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


"BeardT" wrote in message
...
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)

Try something like

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


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



"BeardT" wrote in message
...
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


"Nikos Yannacopoulos" wrote:

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


"BeardT" wrote in message
...
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)

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

Tom

"Chip Pearson" wrote:

Try something like

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


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



"BeardT" wrote in message
...
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


"Nikos Yannacopoulos" wrote:

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


"BeardT" wrote in message
...
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)