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?

Try Range(Range("A5"),Cells(Rows.Count,3).End(xlUp)).Select. You need the Range wrap around the A5 in this case. — Scott Holtzman
– Scott Holtzman, Commented Mar 18, 2016 at 12:55

Community · Accepted Answer · 2017-05-23 12:07:47Z

4

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.}

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Mar 18, 2016 at 13:05

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

BMRobin Over a year ago

Thank you very much @Jeeped. This worked perfectly. I appreciate your detailed explanation about extending the selection across all columns.

Gary's Student · Accepted Answer · 2016-03-18 13:07:43Z

1

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

answered Mar 18, 2016 at 13:07

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

1 Comment

BMRobin Over a year ago

Thank you @Gary's Student. I appreciate your detailed breakdown aspect of your code. Very logical.

Collectives™ on Stack Overflow

Select range cells with blank rows

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related