Selecting Multiple Ranges using Cells(i, j) Notation

I am trying to select multiple ranges in an Excel macro. The ranges are being generated from values of integers created elsewhere.

I have seen examples to select multiple ranges using the standard "A1:A5" notation, but cannot get it to work for the Cells(i, j) notation. Here is the offending code.



When the last line is executed, I get the following error:

Run Time Error 104
Method 'Range' of object '_Global' failed

Of course the help menu in Visual Basic for Excel is most unhelpful.

Any suggestions on how I can make this work would be greatly appreciated.

Thanks,

Michael Pepera

Are you sure it shouldn't be:

Yes you are right. However, the problem still remains the same!

Thanks for the reply,

Michael Pepera

This worked for me:

Set varRangeSelect1 = Range(Cells(10, 2), Cells(11, 2))
Set varRangeSelect2 = Range(Cells(13, 2), Cells(intCountRow, 2))
Set varRangeSelect3 = Union(varRangeSelect1, varRangeSelect2)
varRangeSelect.Select

This would also work:

Set varRangeSelect3 = Union(Range(Cells(10,2), Cells(11,2)), Range(Cells(13,2), Cells(intCountRow,2)))
varRangeSelect3.Select

Hope that helps you out.

Thanks for the help! Now that I look back at the code, I ended doing something slightly different in order to work around the problem.

However, it is good to know how this will function for the future.