Rows in named range?

[IanC]

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


Many thanks.

 

[Rick Rothstein]

Use these...

Range("NamedRange").Rows.Count

Range("NamedRange").Columns.Count

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

 

[Tim Williams]

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

Tim

 

[Rick Rothstein]

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 Rothstein]

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)

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.

 

[IanC]

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.