Select multiple ranges with VBA
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 rng.select
But if you intend on doing something with that range then skip the
.Select and just do what is wanted, ie
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("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
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: