Macro - relative end of data

[Guest]

I want to create a macro that looks for the last ROW of data (which will
always vary) and allows me to copy and paste columns of real data between
spreadsheets. When I RECORD a macro to study the code, it always gives me an
absolute cell reference. How can I modify that macro to point to the
relative end of data? Thanks!

 

[Bob Phillips]

cLastRow = Cells(Rows.Count,"A").End(xlUp).Row

you can then use that in a Range statements, like

Range("A" & cLAstRow)

or something similar

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks for the speedy reply, Bob. I'll give it a try!

 

[Guest]

Bob - that worked great! Thanks. Of course, I have another question! What
would be the code to find the last COLUMN of data? I tried to modify your
code, but the END statement is tripping me up (I think!). Thanks much. What
I'm trying to do is construct the actual last CELL of information.

 

[Bob Phillips]

The last cell of a used range is best found with

Dim LastRow As Long
Dim LastCol As Long
Dim LastCell As Range
LastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
LastCol = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
Set LastCell = Cells(LastRow, LastCol)

The reason for this is that last cell may be empty (say A25 has data, H20
has data, but the last cell in that area is H25), and the previous method
won't find empty cells.

--

HTH

RP
(remove nothere from the email address if mailing direct)