Back

About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store
Podcast
Search
Contact
Home

 

 

Past Tip of the Day

 

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. Buy it here to save $12 off the $39 cover price.


By Bill Jelen on 08-Jul-2002

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

 

Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.