Simplify Naming a Range
July 08, 2002 - by Bill Jelen
I'm generating tables, same number of columns but each time different number of rows. I can determine without problem the last row and the last column to make the calculations I need in a macro. Now the purpose is to add in this macro the possibility to name the table (with the same name each time), but in the macro the cells range associated to the worksheet is R1C1:RxCy, I don't know how to write the range using the values lastrow and lastcolumn (which represent the last column and row of the table) with the RC system (I don't even know if it's possible).
Most VBA programmers would use the following method, which uses the Names.Add method.
Sub Method1() LastRow = Cells(65536, 1).end(xlUp).row LastCol = Cells(1, 255).End(xlToLeft).column MyArea = "='" & ActiveSheet.Name & "'!R1C1:R" & LastRow & "C" & LastCol ActiveWorkbook.Names.Add Name:="TheData", RefersToR1C1:=MyArea End Sub
There is an easier method for assigning a range name. The following four statements are all equivalent. The last two are vastly easier:
ActiveWorkbook.Names.Add Name:="TheData", RefersToR1C1:="=Sheet1!R1C1:R10C5" ActiveWorkbook.Names.Add Name:="TheData", RefersTo:="=Sheet1!A1:E10" Range("A1:E10").Name = "TheData" Cells(1, 1).Resize(10, 5).Name = "TheData"
Jac - I would adapt the last line for use:
Sub Method2() LastRow = Cells(65536, 1).end(xlUp).row LastCol = Cells(1, 255).End(xlToLeft).column Cells(1, 1).Resize(LastRow, LastCol).Name = "TheData" End Sub
I learned this shortcut for assigning range names from Bovey & Bullen's Excel 2002 VBA Programmer's Reference. The 2002 version of this book runs circles around the 2000 version. For my money, any Excel VBA programmer needs that book at his right hand. My copy is dog-eared and never out of reach.