Assigning two ranges from separate sheets to a variable

Question

I have 3 worksheets and I am trying to assign a range to a variable on one of the sheets, based on ranges in two other different worksheets. Here is my code:

Sub Combine()

    Dim range1 As Range
    Dim range2 As Range
    Dim ID As Range

    Set range1 = Worksheets(3).Range("A2:A")
    Set range2 = Worksheets(4).Range("A2:A")
    Set newRng = Worksheets(6).Range(range1, range2)

End Sub

I'm getting back a

Run-time error '1004'

Any suggestions?

The Range method that takes in two other ranges `Range(Range r1, Range r2)` expects those two ranges to be on the same sheet as each other and as the worksheet it was called on. You've defined range1 from sheet 3 and range2 from sheet 4, so it fails, and called it on a completely different sheet 6. What were you expecting the code to do, so I can help you achieve what you expected? — Mikegrann, Jul 19 '16 at 18:19
@Mikegrann I want to assign both ranges from separate sheets to a single variable, I think the first answer may work, I'm trying it now. — H5470, Jul 19 '16 at 18:23

score 1 · Accepted Answer · edited May 23 '17 at 10:33

1

A range can't span multiple worksheets.

This may work, depending on what you ultimately need to do:

Set newRng = Worksheets(6).Range(range1.Address, range1.Address)

But, since these ranges have the same address in your example, I think what you want is not a Range object combining them, but some other data structure, like an array, collection, or dictionary.

NOTE Your ranges are not valid to begin with, Range("A2:A") is not valid, so you'll need to fix that. See here for reliable ways to find the "last" cell in a range. I've modified it to bring in the entire column A (except A1) but you will probably want to fine-tune that.

newRange will have to be a different data type for this to work without raising a Mismatch error, for example a Collection:

Sub Combine()
    Dim coll as New Collection
    Dim range1 As Range
    Dim range2 As Range
    Dim ID As Range

    coll.Add Worksheets(3).Range("A2:A" & Rows.Count)
    coll.Add Worksheets(4).Range("A2:A" & Rows.Count)
    Set newRng = coll
End Sub

Or as an array of range:

Sub combine()
Dim newRange(1) As Range


Set r1 = Worksheets(3).Range("A2:A" & Rows.Count)
Set r2 = Worksheets(4).Range("A2:A" & Rows.Count)

Set newRange(0) = r1
Set newRange(1) = r2
End Sub

Using the array example above, you can then assign the values to another location, modify as needed:

Worksheets(4).Range("B1").Value = newRange(0).Value
Worksheets(4).Range("B2").Value = newRange(1).Value

edited May 23 '17 at 10:33

Community

1
1

answered Jul 19 '16 at 18:20

David Zemens

53,033
11
81
130

I am getting an error back from the Collection, is it because as the answer below pointed out, that "A2:A" is not valid? – H5470 Jul 19 '16 at 18:28
This will give the same 1104 error ` Set r1 = Worksheets(3).Range("A2:A")` – cyboashu Jul 19 '16 at 18:29
Yes, if your range is not valid, that's another cause for the same error. – David Zemens Jul 19 '16 at 18:31
I've modified it to bring in the entire column A (except A1) but you will probably want to fine-tune that. – David Zemens Jul 19 '16 at 18:33
@cyboashu I want to grab the last row in column A on both sheets 3 and 4 dynamically and then make then combine them. I'm trying to avoid copying and pasting both ranges to a new location by making them a variable and then working from that. – H5470 Jul 19 '16 at 18:35
Append column A sheet 3 to column A sheet 4 – H5470 Jul 19 '16 at 18:51

Assigning two ranges from separate sheets to a variable

1 Answers1