name a range which varies in size
Posted by GJT on April 06, 2000 11:00 AM
How do I name a range which varies in size (columns & rows) each time a macro is run. For example I would like to name the range A1:C10 "my_range" (simple - I would use AddRange) ;however, next time I run the macro the range may be A1:G20.
Posted by Ivan Moala on April 06, 2000 4:24 PM
Hi
Try something like this ??
Sub Tester()
Dim Adr As String
Adr = Range("A1").CurrentRegion.Address
ActiveWorkbook.Names.Add Name:="My_Range", RefersTo:="=Sheet1!" & Adr
'Remove ' to see msg address for testing
'MsgBox Adr
End Sub
Ivan
Posted by Celia on April 06, 2000 4:30 PM
GJT
VBA code for what you want varies depending upon the content/make-up of your range.
If you just want to set the range as the "current region" with cell A1 as the start, then :-
Set myRange = Range("A1").CurrentRegion
If you want to set the range as the worksheet's "used range", then :-
Set myrange = ActiveSheet.UsedRange
If your range is not the current region or the used range, then the code depends (among other things) upon whether or not :
_it may contain any blank cells/rows/columns
_the top left-hand cell is always the same
_there is always more than one column and more than one row.
-there may be columns/rows on the worksheet with data but which are not to be included.
Celia
