Rows in named range?

  • Thread starter Thread starter IanC
  • Start date Start date
I

IanC

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


Many thanks.
 
Use these...

Range("NamedRange").Rows.Count

Range("NamedRange").Columns.Count

changing my example "NamedRange" to your actual named range's name.
 
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.
 
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 said:
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.
Click to expand...
 
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.
 
Back
Top