"relative reference"

[Guest]

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

 

[zackb]

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.

 

[Bob Phillips]

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)