Select range cells with blank rows

Question

I've been having difficulties with figuring out how to code this select range macro to include blank rows. The worksheet is a chart with variable number of columns and rows. This is what I have so far:

Sub Macro1()
    Range("A5").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
End Sub

The selection step in the macro will only go so far as that blank row and won't go any further (obviously because it's blank hehe). For that reason, I tried adapting this discontiguous type of code but with no luck:

Sub SelectRangeDown_Discontiguous()
    Range("A5", Range("A65536").End(xlUp)).Select
End Sub

I was hoping someone could help me figure out the best way of writing this code? Am I on the right path?

Answer 1

If you are using .End(xlToRight) or .End(xlDown) you are going to stop at blank cells. If you are using Range("A65536").End(xlUp) then you are only selecting a single column but you are getting everything from A5 down to the last populated cell and bypassing interim blank cells. Extend this latter method laterally.

Sub Macro1()
    with Range("A5")
        .resize(cells(rows.count, "A").end(xlup).row - (.row - 1), _
                cells(5, columns.count).end(xltoleft).column - (.column - 1)).Copy
    end with
End Sub

This would be better with a .Parent Worksheet Object.

You do not need to .Select method something in order to .Copy it¹.

---

¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

Answer 2

Consider:

Sub whatever()
    Dim r1 As Range, r2 As Range

    ActiveSheet.UsedRange
    Set r1 = Range(Cells(5, 1), Cells(Rows.Count, Columns.Count))
    Set r2 = Intersect(r1, ActiveSheet.UsedRange)

End Sub