Creating an XL Range on the fly

[Chris Parker]

I want to define a range on the fly in VBA. The range is dynamic and
constantly changes . I know how to select this but I'm having problems
defining the range.

Can anyone assist ?

Thanks in anticipation.

 

[Patrick Molloy]

You don't say how the range size is determined.
Lets say the anchor point is a cell range named "anchor"
The followign code should be entered into a standard
module:-

Option Explicit
Const ANCHOR As String = "Anchor"
Const PALEYELLOW As Long = "36"
Const LIGHTGREY As Long = 15

Sub test()

SetRange ANCHOR, 8, 3

End Sub

Sub SetRange(sRangeName As String, lRows As Long, lCols
As Long)

Dim rAnchor As Range
Set rAnchor = _
ThisWorkbook.Names(sRangeName).RefersToRange

With rAnchor
.Clear
With .Resize(lRows, lCols)
.Name = sRangeName
.Interior.ColorIndex = PALEYELLOW
.Rows(1).Interior.ColorIndex = LIGHTGREY
End With
End With
End Sub

Run test. It calls the procedure SetRange passing to it
the name of the range to be reset, and it's new size.
The SetRange procedure clears the existing range of the
passed name, resets the size, renames it and colors it.

I used a proc thsi way to add some flexibility, but
obviously, as an example, this is necessarily limited.

HTH
Patrick Molloy
Microsoft Excel MVP

demo file available.Ask for "RangeName.xls"

 

[Sharad]

This should work if you are trying to define a named range
for range("D914")

ActiveWorkbook.Names.Add Name:="trial",
RefersToR1C1:="=Sheet1!R9C4:R14C7"
This creates a named range trial on sheet2 for D9 to F14.
if you need to create this string then u can use the
Worksheet Name property and Address Local Property.
hence it will be:
strRefrence = "="+Range("D914").worksheet.name+"!"+Range
("D914").Address (,,xlR1C1).
and u can use

ActiveWorkbook.Names.Add Name:="trial",
RefersToR1C1:=strRefrence


Hope it helps.

 

[Tom Ogilvy]

Dim rng as Range
set rng = Range("A1").CurrentRegion

' if you want a defined name

rng.Name = "MyName"

msgbox Range("MyName").Address(external:=true)

post back with a clearer explanation of your situation if these don't help.