Creating an XL Range on the fly
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"
-----Original Message-----
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.
.
|