"relative reference" question

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

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.

 

[Gord Dibben]

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