r1c1 workaround in macros

[steve]

I want to use a macro to select a column of data where the
number of rows will vary from time to time. The macro
uses r1c1 referrence and will only select the number of
cells that existed at the time the macro was written.

Is there a work around or smarter way to accomplish?

 

[Andy Brown]

"steve",

Try this KB article
http://support.microsoft.com/default.aspx?scid=kb;en-us;148355

Or a dynamic named range
http://www.contextures.com/xlNames01.html

Rgds,
Andy

 

[Andy Brown]

"Steve",

Can't really follow your issue as all the Shift--End--Down stuff is beyond
me due to crappy keyboard.

Maybe this will help some.

Sub Copy_All_There_Is()
Range("C1", Range("C65536").End(xlUp)).Copy
Range("C65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub