How to count rows in non-contiguous range

[mcomp72]

I need to count the number of rows in a non-contiguous range, such as: “C13, D17:E18, F22, G25, C29:F33”

If I select those cells on my worksheet using Ctrl and the left mouse button, and then run the following code:

Dim Rng As Range
 
Set Rng = Selection
 
Debug.Print Rng.Rows.Count

The result in the Immediate window is “1”. So that obviously doesn’t work with a non-contiguous range. Is there some other way it can be done?

 

[footoo]

Dim a As Range, c&
For Each a In Selection.Areas
    c = c + a.Rows.Count
Next
Debug.Print c

Note: this will total the rows in each area selected. If the selection has overlapping areas, it will count the overlaps separately.
If this is a problem, post again.

 

[Peter_SSs]

I need to count the number of rows in a non-contiguous range,

You would also need to define exactly what you mean by "count the rows".

For example if your range is "A1:A3,C2:C4", is the row count
6 because there are 3 rows in each area, or
4 because the range actually only occupies 4 rows on the worksheet?

 

[footoo]

Note: this will total the rows in each area selected. If the selection has overlapping areas, it will count the overlaps separately.
If this is a problem, post again.

If you want the rows for overlapping selection counted once only:

Dim d As Object, a As Range, i&
Set d = CreateObject("Scripting.Dictionary")
For Each a In Selection.Areas
    For i = 1 To a.Cells.Count
        d(a(i).Row) = i
    Next
Next
Debug.Print d.Count

 

[mcomp72]

Hi Peter & footoo. Thanks for the replies. Yes, I did want rows for overlapping selections to be counted only once, so footoo, the code in your second reply looks like it will work for me. Although I will need to test it on Mac, too. The workbook I am making needs to work on both Windows & Mac, and I am not sure if CreateObject always works correctly on Mac. (There are many things that don't work the same in VBA on Mac, unfortunately.)

Another question: I see you listed a variable with a '&' after it in your code. I have not seen this before. I'm curious what the '&' signifies?

 

[footoo]

Another question: I see you listed a variable with a '&' after it in your code. I have not seen this before. I'm curious what the '&' signifies?

& represents the variable type: Long

Other variable type characters:
Single !
Double #
Integer %
Currency @
String $

 

[footoo]

Although I will need to test it on Mac, too. The workbook I am making needs to work on both Windows & Mac, and I am not sure if CreateObject always works correctly on Mac.

Some possible fixes here: https://www.google.com/search?q=exc....69i57j0l2.23576j0j8&sourceid=chrome&ie=UTF-8

 

[footoo]

Here's a possibility without using Dictionary:

Dim a As Range, c&
For Each a In Intersect(Selection.EntireRow, Columns(1))
    c = c + a.Rows.Count
Next
Debug.Print c

 

[Peter_SSs]

Here's a possibility without using Dictionary:

Dim a As Range, c&
For Each a In Intersect(Selection.EntireRow, Columns(1))
    c = c + a.Rows.Count
Next
Debug.Print c

Comments:
I suspect that you meant ...
For Each a In Intersect(Selection.EntireRow, Columns(1)).Areas
.. for as it stands each 'a' in your code is a single cell so a.Rows.Count is 1 every time so that line would be as well to be c = c + 1

In any case, it can be done in a single line since the intersection areas in column A will never overlap

Sub CountRowsSelection()
  Debug.Print Intersect(Selection.EntireRow, Columns("A")).Cells.Count
End Sub

or if it is not actually the selection but rather a range like was mentioned in line 1 of post 1

Sub CoutRowsRange()
  Debug.Print Intersect(Range("C13, D17:E18, F22, G25, C29:F33").EntireRow, Columns("A")).Cells.Count
End Sub