CODEWITHSUNDEEP
excelrowsvba
jivko
jivko

Reputation: 430

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

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

Answers (2)

WGS
WGS

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

chris neilsen
chris neilsen

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

Related Questions