Select multiple ranges with VBA

user_5298189 0
Viewed 9164

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.

vba excel
3 Answers

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

user_48515900

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
user_62023430

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

user_59101690
Related