Incorrect Rows.Count in an interrupted range (Excel VBA)

0
votes

I'm trying to get the Rows.Count of a range which consists of two combined ranges:

Set rng = Union(Range1,Range2)

Unfortunately the rng.Rows.Count returns the Rows.Count of Range1, and I would expect it to return Rows.Count of Range1+Range2.

The actual range I tested it with is this: $A$27:$G$41,$A$43:$G$43

Its Rows.Count property returns 15 but in this range there are 16 rows.

Any ideas how to get the correct Rows.Count without much juggling?

2
excelrowsvba
The two solutions below will work, provided that your ranges do not have any overlap. – sam092
@sam092: Yeah, that's a concern. However, if there are overlaps, a comparison of Rows.Count of each area and returning the highest value might suffice. ;) – NullDev
@BK201 not necessarily. Try Range("A1:C8") and Range("A2:B9") – sam092

2 Answers

2
votes

Try this

Dim arr as range
Dim rws as long

For each arr in rng.areas
    Rws = rws + arr.rows.count
Next
0
votes

Try this:

Sub Test()
    Dim rng As Range
    Res = 0
    Set rng1 = Range("A27:G41")
    Set rng2 = Range("A43:G43")
    Set rng = Union(rng1, rng2)
    For Each area In rng.Areas
        Res = Res + area.Rows.Count
        Debug.Print Res
    Next area
End Sub

Hope this helps.