Rows in named range?
 

How can I determine (using VBA) how many rows and columns are in a named
range?


Many thanks.

--
Ian
--

Rows in named range?
 

Use these...

Range("NamedRange").Rows.Count

Range("NamedRange").Columns.Count

changing my example "NamedRange" to your actual named range's name.

--
Rick (MVP - Excel)


"IanC" wrote in message ...
How can I determine (using VBA) how many rows and columns are in a named
range?


Many thanks.

--
Ian
--

Rows in named range?
 

....and watch out for non-contiguous ranges.

Tim

"Rick Rothstein" wrote in message
...
Use these...

Range("NamedRange").Rows.Count

Range("NamedRange").Columns.Count

changing my example "NamedRange" to your actual named range's name.

--
Rick (MVP - Excel)


"IanC" wrote in message ...
How can I determine (using VBA) how many rows and columns are in a named
range?


Many thanks.

--
Ian
--

Rows in named range?
 

Good point! These should work for both contiguous and non-contiguous
ranges...

Dim A As Range
......
For Each A In Range("NamedRange").Areas
TotalRows = TotalRows + A.Rows.Count
Next
For Each A In Range("NamedRange").Areas
TotalColumns = TotalColumns + A.Columns.Count
Next

Note, however, these return the total of the number of rows and/or columns
in each area for non-contiguous ranges, whether the row ranges and/or column
ranges overlap or not.

--
Rick (MVP - Excel)


"Tim Williams" wrote in message
...
...and watch out for non-contiguous ranges.

Tim

"Rick Rothstein" wrote in message
...
Use these...

Range("NamedRange").Rows.Count

Range("NamedRange").Columns.Count

changing my example "NamedRange" to your actual named range's name.

--
Rick (MVP - Excel)


"IanC" wrote in message
...
How can I determine (using VBA) how many rows and columns are in a named
range?


Many thanks.

--
Ian
--

Rows in named range?
 

And if the OP wants total entire rows and total entire columns (these would
be the row and column ranges formed by the EntireRow and EntireColumn
properties of each cell in the NamedRange), he would need something like
this...

Dim A As Range, R As Range, C As Range
Dim TotalRows As Long, TotalColumns As Long
......
For Each A In Range("NamedRange").Areas
If R Is Nothing Then
Set R = A.EntireRow
Else
Set R = Union(R, A.EntireRow)
End If
If C Is Nothing Then
Set C = A.EntireColumn
Else
Set C = Union(C, A.EntireColumn)
End If
Next
For Each A In R.Areas
TotalRows = TotalRows + A.Rows.Count
Next
For Each A In C.Areas
TotalColumns = TotalColumns + A.Columns.Count
Next

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Good point! These should work for both contiguous and non-contiguous
ranges...

Dim A As Range
.....
For Each A In Range("NamedRange").Areas
TotalRows = TotalRows + A.Rows.Count
Next
For Each A In Range("NamedRange").Areas
TotalColumns = TotalColumns + A.Columns.Count
Next

Note, however, these return the total of the number of rows and/or columns
in each area for non-contiguous ranges, whether the row ranges and/or
column ranges overlap or not.

--
Rick (MVP - Excel)


"Tim Williams" wrote in message
...
...and watch out for non-contiguous ranges.

Tim

"Rick Rothstein" wrote in message
...
Use these...

Range("NamedRange").Rows.Count

Range("NamedRange").Columns.Count

changing my example "NamedRange" to your actual named range's name.

--
Rick (MVP - Excel)


"IanC" wrote in message
...
How can I determine (using VBA) how many rows and columns are in a
named range?


Many thanks.

--
Ian
--

Rows in named range?
 

Thanks Rick. This is just what I needed. Unfortunately, I've met an
unexpected problem, but I'll put that in another post as it effectively
unrelated.

--
Ian
--

"Rick Rothstein" wrote in message
...
Use these...

Range("NamedRange").Rows.Count

Range("NamedRange").Columns.Count

changing my example "NamedRange" to your actual named range's name.

--
Rick (MVP - Excel)


"IanC" wrote in message ...
How can I determine (using VBA) how many rows and columns are in a named
range?


Many thanks.

--
Ian
--