selecting changing cell ranges

J

jtman

Hi,

I've run into a real problem, where there may actually be a simple
solution. I have created a table in Excel, in which I will continue to
enter small amounts of data over time. What I want to do is take the
contents of the last 20 cells in one column and paste them into a
column of cells on another sheet. I can't use a specified range for
this, as anytime I enter a new row of data, the range would of course
change. Is there any kind of function for doing this? Or, I was
thinking, maybe there is a way to activate a specified range upon entry
or exit in a cell. Any ideas? Thanks in advance for any help.

Jeff
 
N

Norman Jones

Hi JT,

Try something like:

'=============>>
Public Sub Tester()
Dim LastRow As Long
Dim rng As Range
Const col As String = "A" '<<==== CHANGE

LastRow = Cells(Rows.Count, col).End(xlUp).Row

Set rng = Cells(LastRow, col).Offset(-19).Resize(20)

rng.Interior.ColorIndex = 6

End Sub
'<<=============
 
Top