Naming a range

  • Thread starter Thread starter Andrew B
  • Start date Start date
A

Andrew B

Hi
I would like to define a range like this:
rnga = Range("D61:D96", "D101:D136", "D141:D176").

So I can use it in a spreadsheet formula such as this formula typed into
cell D40:

[D40] = STDEVP(rnga)

I know I can use INSERT -> NAME -> DEFINE to define rnga but I would like
to be able to do this through VBA.

Is this possible ?

Thanks in advance

Andrew Bourke

(e-mail address removed)
 
Andrew,.

the simplest way for you to figure this one out would be to use the macro
recorder. Here's a long winded version.

Sub NameRange()

Dim rngA As Range

Set rngA = Sheets(1).Range("D61:D96")
Set rngA = Union(rngA, Sheets(1).Range("D101:D136"))
Set rngA = Union(rngA, Sheets(1).Range("D141:D176"))

Sheets(1).Names.Add Name:=Sheets(1).Name & "!" & "RangeA", _
RefersToR1C1:=rngA

End Sub

Robin Hammond
www.enhanceddatasystems.com
 
Back
Top