Reputation: 430
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?
Upvotes: 0
Views: 1283
Reputation: 14179
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.
Upvotes: 0
Reputation: 53155
Try this
Dim arr as range
Dim rws as long
For each arr in rng.areas
Rws = rws + arr.rows.count
Next
Upvotes: 2