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

[DJA]

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.

 

[ozgrid.com]

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

 

[Max]

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

 

[Teethless mama]

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

 

[Teethless mama]

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