Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"relative reference"
I'm new to the whole macro thing and am looking for a simple way to
understand the "relative reference" when recording a macro. Can someone shed some light on that? also, my macro takes a spreadsheet of 10 columns and x rows and formats it. How would I build the macro so that no matter the number of rows, the macro will still work? would i have to write code for this or will the record macro option work? Any advice would be appreciated. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"relative reference"
Hi there,
Recording macros is a very useful tool. It's great to learn from. It's not, however, dynamic. It will hardcode your references into the procedures written/recorded. So it may not work on all data, all the time. Finding the last row is generally best done from the bottom up. I suggest you use the column that will have the most data in it, we'll assume for a minute that this is column A. This will be the column that we set our last row from in a certain procedure; we will set it to a variable as well. ... Sub LastRow() dim lastRow as Long lastRow = Range("A65536").end(xlup).Row Msgbox lastRow End Sub This is how you get the last used row in a column. You can use the UsedRange property of the worksheet, but Excel can misinterpret this and your results would be skewed. For the last column, we'd also assume that your largest column will be dictated by row 1 where (most likely) your headers will be. ... Sub LastColumn() dim lastCol as Long lastCol = Range("IV1").end(xltoleft).Column Msgbox lastCol End Sub With these two figures we can select the entire range, copy it, manipulate it, whatever. A combined effort would look like this ... Sub LastRow() dim lastRow as Long, lastCol as Long, rngAll as Range lastRow = Range("A65536").end(xlup).Row lastCol = Range("IV1").end(xltoleft).Column Set rngAll = Range(cells(1, 1), cells(lastRow, lastCol)) rngAll.Select Msgbox "The whole range of your data is:" & vbcrlf & rngAll.address End Sub These examples should give you a basic understanding of how a recorded macro may not always work. Recordings are great to study and one of the most underestimated tools in Excel's arsenal (in my humble opinion), but flawed in the end. If there is anything else you'd like explained, please don't hesitate to ask. :) -- Regards, Zack Barresse, aka firefytr "newarkj" wrote in message ... I'm new to the whole macro thing and am looking for a simple way to understand the "relative reference" when recording a macro. Can someone shed some light on that? also, my macro takes a spreadsheet of 10 columns and x rows and formats it. How would I build the macro so that no matter the number of rows, the macro will still work? would i have to write code for this or will the record macro option work? Any advice would be appreciated. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"relative reference"
Do you understand relative/absolute in terms of a worksheet cell reference?
If so, what exactly do you mean regarding recording a macro? You can find the last row of a range like this Set rng = Activesheet.UsedRange iLastRow = rng(rng.Count).Row Then you can set a range and for mat the whole range. For instance, this gets all cells in column A Set rngColA = Range("A1:A" & iLastRow) -- HTH RP (remove nothere from the email address if mailing direct) "newarkj" wrote in message ... I'm new to the whole macro thing and am looking for a simple way to understand the "relative reference" when recording a macro. Can someone shed some light on that? also, my macro takes a spreadsheet of 10 columns and x rows and formats it. How would I build the macro so that no matter the number of rows, the macro will still work? would i have to write code for this or will the record macro option work? Any advice would be appreciated. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Lost "Relative Reference" button in Excel | Excel Discussion (Misc queries) | |||
A "relative" absolute cell reference? | Excel Discussion (Misc queries) | |||
Excel VBA - go to worksheet "name" based on a cell reference ="Name" | Excel Programming | |||
Change "relative" to "absolute" (database) | Excel Programming |