"relative reference" question
 

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

Hi there,

Your whole intent is unclear. Can you specify the Ranges/Addresses to which
you are referring? Give some examples. And if need be, post your code.


--
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

Try both ways when recording and you will see the difference.

Non-relative will record hard ranges like Range("A1:A23").Select

Relative will record ranges like Range(Selection,
Selection.End(xlDown)).Select

and ActiveCell.Offset(173, 5).Range("A1").Select

The best way to find the bottom of a range is to start from the bottom of the
sheet and come up to the last filled cell(s)

e.g. to find last filled cell in Column A

Sub findbottom()
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0).Select
End Sub

If you record CTRL + End to go to bottom of used range you get

ActiveCell.SpecialCells(xlLastCell).Select

Be careful with this one. Excel can overestimate the used range of a sheet.


Gord Dibben Excel MVP




On Wed, 27 Apr 2005 08:18:03 -0700, "newarkj"
wrote:

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