Worksheet Cell Range Name within VB Subroutine

  • Thread starter Thread starter Jay Somerset
  • Start date Start date
J

Jay Somerset

How does one refer to a named area in a worksheet from within a VB macro
subroutine? I know how to refer to a selection, but not a block of cells
that has been named using the Insert|Name|Define dialog.

Example: I have a 3x3 block of cells named AA. How can I refer to the
individual cells within AA [e.g. AA(1,2)] within the VB coding of a macro
(subroutine)? How can I make the name AA visible within the subroutine, and
define it a s 3x3 array?

Thanks.
 
A named area is just a range with a name associated with it:

Sub name_it()
Dim n As Name
Dim r As Range
Dim s As String

For Each n In ThisWorkbook.Names
MsgBox (n.Name)
s = Range(n).Address
MsgBox (s)
Next n

End Sub

n behaves like a string. You can loop until you have the right name and then
SET r=Range(n) and use r like any other range, use all the usual methods
like Clear, use Offsets, Select, etc.
 
if its named AA the vb name is range("AA")
--
paul
remove nospam for email addy!



Gary''s Student said:
A named area is just a range with a name associated with it:

Sub name_it()
Dim n As Name
Dim r As Range
Dim s As String

For Each n In ThisWorkbook.Names
MsgBox (n.Name)
s = Range(n).Address
MsgBox (s)
Next n

End Sub

n behaves like a string. You can loop until you have the right name and then
SET r=Range(n) and use r like any other range, use all the usual methods
like Clear, use Offsets, Select, etc.
--
Gary's Student


Jay Somerset said:
How does one refer to a named area in a worksheet from within a VB macro
subroutine? I know how to refer to a selection, but not a block of cells
that has been named using the Insert|Name|Define dialog.

Example: I have a 3x3 block of cells named AA. How can I refer to the
individual cells within AA [e.g. AA(1,2)] within the VB coding of a macro
(subroutine)? How can I make the name AA visible within the subroutine, and
define it a s 3x3 array?

Thanks.
Click to expand...
Click to expand...
 
How does one refer to a named area in a worksheet from within a VB macro
subroutine?
Having named B4:D6 MyNamedRange

Range("MyNamedRange").select

How can I refer to the individual cells within AA [e.g. AA(1,2)] within the
VB coding of a macro (subroutine)?

Set MyRng = Range("MyNamedRange")
MyRng(7).select <<< would select the row 3 column 1 of the 3 X 3 block
B4:D6
 
How does one refer to a named area in a worksheet from within a VB macro
subroutine? I know how to refer to a selection, but not a block of cells
that has been named using the Insert|Name|Define dialog.

Example: I have a 3x3 block of cells named AA. How can I refer to the
individual cells within AA [e.g. AA(1,2)] within the VB coding of a macro
(subroutine)? How can I make the name AA visible within the subroutine, and
define it a s 3x3 array?

Thanks.
Click to expand...

The worksheet names are available within a VB macro. Try using...
Range("name").Cells(i, j).Value to refer to the (i,j) member of an
array (cell range) to which the name "name" has been assigned on the
worksheet.
 
How does one refer to a named area in a worksheet from within a VB macro
subroutine? I know how to refer to a selection, but not a block of cells
that has been named using the Insert|Name|Define dialog.

Example: I have a 3x3 block of cells named AA. How can I refer to the
individual cells within AA [e.g. AA(1,2)] within the VB coding of a macro
(subroutine)? How can I make the name AA visible within the subroutine, and
define it a s 3x3 array?

Thanks.
Click to expand...

The worksheet names are available within a VB macro. Try using...
Range("name").Cells(i, j).Value to refer to the (i,j) member of an
array (cell range) to which the name "name" has been assigned on the
worksheet.
 
Back
Top