Select multiple ranges with VBA

excel vba union multiple ranges
copy multiple ranges vba
range vba
vba range with variable row number
how to reference multiple ranges in vba
excel vba union non contiguous range
vba union named ranges
excel range multiple columns

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.


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

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

Select and Copy multiple ranges with VBA, Try the code below, explanation inside the code as comments: Option Explicit Sub CopyMultipleRanges() Dim iLastRow As Long Dim sh As  By using the appropriate method, you can easily refer to multiple ranges. Use the Range and Union methods to refer to any group of ranges. Use the Areas property to refer to the group of ranges selected on a worksheet. Using the Range Property. You can refer to multiple ranges with the Range property by inserting commas between two or more references. The following example clears the contents of three ranges on Sheet1.

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

VBA Union - Join Multiple Ranges, The need to combine multiple range strings is probably most common when selecting a large number of non contiguous cells with VBA. The following is an example of using Union. First you declare some range variables, then set the range variables equal to something, and finally combine them using Union. We can create a union of scattered range to one to perform a similar task for all the union ranges. To select multiple ranges of cells we can usually use RANGE object. For example, if we want to select the range of cells from A1 to B5 and from B3 to D5, we can write the VBA code like below.

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

Macros in Excel : Selecting Multiple Ranges in Excel VBA, You can combine multiple ranges into one Range object using the Union method. The following example creates a Range object called myMultipleRange, defines it as the ranges A1:B2 and C3:D4, and then formats the combined ranges as bold. The need to combine multiple range strings is probably most common when selecting a large number of non contiguous cells with VBA. One way to join multiple range strings is using VBA’s Union function. The syntax for the Union function is: 1

How to select Multiple Range in Excel worksheet, object. The cell references need to be enclosed in double quotes. In the image below we can see how we can select them. Select multiple rows which are adjacent to each other. In the code below we are selecting 3rd and 4th at the same time. Sub SelectMultipleRows() 'Select continious rows Rows("3:4").Select End Sub In the image below we can see that both the rows are selected at the same time.

Select cell contents in Excel, This code works fine for me: Range("B8:C10,H8:H10"). You are currently viewing the Excel VBA section of the Wrox Programmer to Select multiple ranges. Select but I want to have it in this format: Range(Cells(8, 2), Cells(10, 3​), Cells(8,  Sub SelectCell() Range("A1").Select End Sub. The above code has the mandatory ‘Sub’ and ‘End Sub’ part, and a line of code that selects cell A1. Range(“A1”) tells VBA the address of the cell that we want to refer to. Select is a method of the Range object and selects the cells/range specified in the Range object. The cell references need to be enclosed in double quotes.

Working with Cells and Ranges in Excel VBA (Select, Copy, Move , This video demonstrates how to reference a range of cells in Excel VBA: a single range and Duration: 2:05 Posted: Jan 18, 2018 This one is called Select, and it performs a very simple function: it selects the cells you’ve referred to with the Range (or Cells) object. Here’s an example: Range("A1:D2").Select. When you run the macro, you’ll see that the cells defined by the range have now been selected:

  • Range("A84:B" & LastRow & ",D84:E" & LastRow & ",H84:J" & LastRow).Select
  • Range( Replace("A84:B<r>,D84:E<r>,H84:J<r>", "<r>", LastRow) ).Select
  • 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