Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an XL Range on the fly
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an XL Range on the fly
This should work if you are trying to define a named range
for range("D9:D14") 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("D9:D14").worksheet.name+"!"+Range ("D9:D14").Address (,,xlR1C1). and u can use ActiveWorkbook.Names.Add Name:="trial", RefersToR1C1:=strRefrence Hope it helps. -----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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an XL Range on the fly
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. -- Regards, Tom Ogilvy "Chris Parker" wrote in message om... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a named range? | Excel Discussion (Misc queries) | |||
creating range for chart | Excel Discussion (Misc queries) | |||
Need help with creating a Range Name syntax | Setting up and Configuration of Excel | |||
Need help with creating a Range Name syntax | Setting up and Configuration of Excel | |||
Can I use named range in data range box when creating pie chart? | Charts and Charting in Excel |