Extend row down by 1

[Sarah (OGI)]

I need to identify and highlight the last row of data in my worksheet, and
extend the whole row down by one row, to capture any new data that might be
available.

How can this be written in vb code? (I need to incorporate this step into a
macro, which will then be run every week with the source data also being
updated weekly)

Many thanks in advance.

 

[Mike H]

Sarah,

Say you expanding column of data are in column A, this will set a range for
that growing data

lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A2:A" & lastrow)

Mike

 

[JLGWhiz]

Adding to Mike's response, the code he provided will identify the last row of
data in column A and assign it to a variable at the instant that the code is
run. If you then add three rows of data, the actual last row will have
changed but the value of the variable will not change unless those lines of
code are executed again. So, you can either use the last row variable as a
reference point and offset from it as your range size increases, or contruct
your code so that the variable value is recalculated by runing the code lines
after any change in the range size.