Select multiple ranges with VBA

Question

I need to select multiple ranges in a worksheet to run various VBA code on them. The ranges will always begin on row 84 but the end depends on how far down the data goes. I've been selecting these ranges separately using code like this:

Sub SelectRange()
Dim LastRow As Integer
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range("A84:B" & LastRow).Select
End Sub

That works fine, but I can't figure out how to select multiple ranges at once. I've tried everything I can think of:

Range("A84:B", "D84:E", "H84:J" & LastRow).Select
Range("A84:B,D84:E,H84:J" & LastRow).Select
Range("A84:B & LastRow,D84:E & LastRow,H84:J & LastRow").Select

Nothing works. I get a run-time error when running any of those.

Range("A84:B" & LastRow & ",D84:E" & LastRow & ",H84:J" & LastRow).Select — Tim Williams
– Tim Williams, Commented Oct 8, 2019 at 22:35
Range( Replace("A84:B<r>,D84:E<r>,H84:J<r>", "<r>", LastRow) ).Select — Tim Williams
– Tim Williams, Commented Oct 8, 2019 at 23:48

Scott Craner · Accepted Answer · 2019-10-08 22:35:02Z

7

Use UNION:

Dim rng as Range
With ActiveSheet
    set rng = Union(.Range("A84:B" & LastRow),.Range("D84:E" & LastRow),.Range("H84:J" & LastRow))
End With
rng.select

But if you intend on doing something with that range then skip the .Select and just do what is wanted, ie rng.copy

answered Oct 8, 2019 at 22:35

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

Comments

John Shaw · Accepted Answer · 2019-10-09 07:05:00Z

1

Put your dis-continued range address in the first argument of Range object.

For example, Range("A:A,D:D").Select will select column A and column D.

In your case, you may try:

Dim str As String, LastRow As Integer

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
str = "A84:B" & LastRow & ",D84:E" & LastRow & ",H84:J" & LastRow
Range(str).Select

edited Oct 9, 2019 at 7:05

John Shaw

3584 silver badges12 bronze badges

answered Oct 9, 2019 at 2:38

PaichengWu

2,6891 gold badge16 silver badges28 bronze badges

Comments

Frank Ball · Accepted Answer · 2019-10-08 22:38:10Z

0

Range("A84:B & LastRow & "," & "D84:E & LastRow & "," & "H84:J & LastRow").Select

answered Oct 8, 2019 at 22:38

Frank Ball

1,1778 silver badges21 bronze badges

1 Comment

PaichengWu Over a year ago

this answer returns a compile error. may due to the misplacing of double quotes. Try modify to Range("A84:B" & LastRow & "," & "D84:E" & LastRow & "," & "H84:J" & LastRow).Select

Collectives™ on Stack Overflow

Select multiple ranges with VBA

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related