CODEWITHSUNDEEP
excelvba
user2023000
user2023000

Reputation: 25

Excel VBA Union returning original range

I have an issue when trying to use Union(Range, Range). I am trying to copy certain rows from one worksheet and paste them in a new file. My issue is that the Union isn't adding on more rows to the range; it returns the original range. If I flip the order of the parameters, it returns only the .Rows(i + 1) row. My test data has 2 rows that it should copy. The row count at the end is 1. What am I doing wrong?!

Dim lastRow, i As Long
Dim CopyRange As Range
lastRow = ActiveSheet.Rows.count

With Sheets(ActiveSheet.Name)
    lastRow = .Range("A" & .Rows.count).End(xlUp).Row

    For i = 2 To lastRow
        Dim endTime As Date
        endTime = DateValue(Trim(.Range("E" & i).Value))
        If endTime = Date - 1 Then
            If CopyRange Is Nothing Then
                Set CopyRange = .Rows(i + 1)
            Else
                Set CopyRange = Union(CopyRange, .Rows(i + 1))
            End If


        End If
    Next
End With

CopyRange.Copy

Upvotes: 0

Views: 633

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Actually CopyRange contains a number of separate ranges (areas). See here for more information. When you make Union operation with separate rows (I mean that there is another row beetween them) it doesen't actually add Row, but add new Area. So, if you add MsgBox CopyRange.Areas.Count, you will see count of areas (if you will add MsgBox CopyRange.Rows.Count it will get you uncorrect result in case of many areas - it will get count of rows in first area). As a conclusion, your code works well for me, and should works well for you. You can add CopyRange.Select line before CopyRange.Copy and set breakpoint on this line. You will see that union works well

Upvotes: 1

Related Questions