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.

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

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.

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

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 D2:D21 (with D2 active)
Copy n paste this into the formula bar (ie for D2), then press
CTRL+SHIFT+ENTER to confirm the formula into D2:D21 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 D2:D21 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.

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

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.

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

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.