Creating Ranges

  • Thread starter Thread starter Bongard
  • Start date Start date
B

Bongard

Can you create a Range in VBA that is available to be referenced in
spreadsheet formulas? In other words, can you create a range in VBA
that shows up when you click Insert - Name - Define?

Thanks,
Brian
 
Hi
Range("A1:B3").Name = "myRange"

will give you myRange visible in the names dialog box.
regards
Paul
 
You can add it to an individual sheet or to the workbook as a whole using
one of these constructions...

Worksheets(Sheet1).Names.Add "YourNameForTheRange", "=Sheet1!$C$3:$F$6"

ThisWorkbook.Names.Add "YourNameForTheRange", "=Sheet1!$C$3:$F$6"

The absolute cell references are important.
 
Thank you both for your prompt responses. Can I ask why the absolutel
cell references are important for the named range?
 
If you use relative references, the named range ends up being relative to
the active cell.
 
I suppose that makes sense and now I see when I enter relative
references that the named range changes as I move the selection around
the spreadsheet. The final code that I used (if it will help anyone in
the future) is the following

'PosSpread_Q - Found in the Cross Sectional Quintile data
Range("CA2:DD2").Select
PosSpread = Selection.Find(What:="PtoE SN Q",
After:=ActiveCell).Address(RowAbsolute:=False, ColumnAbsolute:=False)
If Range(PosSpread).Column > 26 Then PosSpread1 = Left(PosSpread, 2)
Else PosSpread1 = Left(PosSpread, 1)
PosSpreadRange = "$" & PosSpread1 & "$4:$" & PosSpread1 & "$499"
ActiveWorkbook.Names.Add Name:="PosSpread_Q", RefersTo:="=OFFSET(Data!
$" & PosSpread1 & "$4,0,0,COUNTA(Data!" & PosSpreadRange & "),1)"

Thanks guys for your help,
Brian
 
Back
Top