Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - relative end of data
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - relative end of data
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) "Nancy C" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - relative end of data
Thanks for the speedy reply, Bob. I'll give it a try!
"Bob Phillips" wrote: 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) "Nancy C" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - relative end of data
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" wrote: 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) "Nancy C" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - relative end of data
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) "Nancy C" wrote in message ... 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" wrote: 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) "Nancy C" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The Relative Macro | Excel Discussion (Misc queries) | |||
relative value in macro | Excel Discussion (Misc queries) | |||
Relative Reference in a Macro | Excel Programming | |||
MACRO Relative Reference | Excel Programming | |||
relative ref. macro | Excel Programming |