how do I copy the last 20 entries in an expanding list

Within a macro, I need to copy the last 20 entries of a list to another
location.
as the list grows, I still only need the last 20 entries to copy to the
location.
in lotus I would write "end down" "up 20" then copy & move.

I can't find a corresponding formula in excel.

when I try using end down in the macro and copy... it seems to always use
the original space rather than the new space as new entries are listed.

Assumes list is Column "A" and is contiguous

Sub CopyLastX()
Dim rRange As Range

Set rRange = Range(Cells(Rows.Count, 1).End _
(xlUp).Offset(-20, 0), Cells(Rows.Count, 1).End(xlUp))

MsgBox rRange.Address
End Sub


--
Regards
Dave Hawley
www.ozgrid.com
"DJA" wrote in message
...
Within a macro, I need to copy the last 20 entries of a list to another
location.
as the list grows, I still only need the last 20 entries to copy to the
location.
in lotus I would write "end down" "up 20" then copy & move.

I can't find a corresponding formula in excel.

when I try using end down in the macro and copy... it seems to always use
the original space rather than the new space as new entries are listed.

I can't find a corresponding formula in excel

You could try this multi-cell array formula
Assume your source data is in A2:B2 down, contiguous & expected to vary up
to say, A2:B1000
Select a 20 cell col range, say D221 (with D2 active)
Copy n paste this into the formula bar (ie for D2), then press
CTRL+SHIFT+ENTER to confirm the formula into D221 at one go (ie multi-cell
array-enter):
=OFFSET(INDIRECT("A"&MAX(IF(A$2:A$1000"",ROW(A$2 :A$1000)))),,COLUMNS($A:A)-1,-20)
Copy D221 across to E21. D2:E21 will return the last 20 rows of the source
data in cols A and B as it expands up to row 1000. The formula monitors the
last filled cell in col A, then grabs 20 rows from there upwards (via the
"-20" height param in the OFFSET). Tested ok here. Success? hit the YES below
--
Max
Singapore
---
"DJA" wrote:
Within a macro, I need to copy the last 20 entries of a list to another
location.
as the list grows, I still only need the last 20 entries to copy to the
location.
in lotus I would write "end down" "up 20" then copy & move.

I can't find a corresponding formula in excel.

when I try using end down in the macro and copy... it seems to always use
the original space rather than the new space as new entries are listed.

Assuming your data in column A

If your data are 'text' value then use this:
=INDEX(A:A,MATCH("zzzzz",A:A)+ROWS($1:1)-20)

copy down to next 19 rows


If your data are 'numeric' value then use this:
=INDEX(A:A,MATCH(10^10,A:A)+ROWS($1:1)-20)

copy down to next 19 rows



"DJA" wrote:

Within a macro, I need to copy the last 20 entries of a list to another
location.
as the list grows, I still only need the last 20 entries to copy to the
location.
in lotus I would write "end down" "up 20" then copy & move.

I can't find a corresponding formula in excel.

when I try using end down in the macro and copy... it seems to always use
the original space rather than the new space as new entries are listed.

If your data are mixed with 'text' and 'numeric' values in a column
example:

cat
dog
24
33
chicken
44
and so on..

=INDEX(A:A,MAX(MATCH("zzzzz",A:A),MATCH(10^10,A:A) )+ROWS($1:1)-20)
copy down to next 19 rows





"DJA" wrote:

Within a macro, I need to copy the last 20 entries of a list to another
location.
as the list grows, I still only need the last 20 entries to copy to the
location.
in lotus I would write "end down" "up 20" then copy & move.

I can't find a corresponding formula in excel.

when I try using end down in the macro and copy... it seems to always use
the original space rather than the new space as new entries are listed.