Selecting a Data Range

[Muleskin57]

Hello,
I'm creating a tool for teachers using a "table" that is formatted as a data range. The last column has a formula that produces a time stamp for entries. I'd like to create a macro that copies all the lines, from cell A2 to the last timestamp in column O to another sheet. So I need to be able to find and select that range. Any ideas?

Thanks.

 

[MARK858]

Change sheet names and destination range to suit

Sheets("Sheet1").Range("A2:O" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row).Copy Sheets("Sheet2").Range("A2")

Assumes that Column A is your longest column

 

[Muleskin57]

Thanks for the quick response, but that seems to select the entire table, doesn't end with the last timestamp produced in column O.

 

[MARK858]

If Column O is the longest column change

Range("A" & Rows.Count)

to

Range("O" & Rows.Count)

as long as the cells below are truly blanks and not formulas returning "".

 

[Muleskin57]

Yes, the cells have a formula returning a "". I need to select A2 to the last non-"" cell in column O.

 

[MARK858]

Dim LstRw As Long
LstRw = Sheets("Sheet1").Columns("O").Find("*", , xlValues, , xlByRows, xlPrevious).Row
Sheets("Sheet1").Range("A2:O" & LstRw).Copy Sheets("Sheet2").Range("A2")