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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Post my named ranges in column A 10
Row and column count last occupied cells in range 2
Excel VBA - counting cells by colors and names to it in two different rows. 0
VBA CreateNames (ignores last 5 rows?) 6
Array with many cells from a row but not in order or together 11
Import multiple workbooks to current workbook in specified row number 0
Count occurances in a range. 5
Delete rows based on contents of Col A 0

Back
Top