Simplify Naming a Range

July 08, 2002 - by Bill Jelen

Jac asks:

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.