Multiple Range Select

[Lamar in Houston]

I have a worksheet which contains varying rows of data from day to day. I need to automate a way for a user to collect the data from all cells in two non-contigous columns, those being B and Y, from row 3 to the last record in those columns.

Below is the track I am on, one that I cannot make work with columns that are non adjacent.

Range (("B3:" & Range("B65536").End(xlUp).Address)), _
(("Y3:" & Range("Y65536").End(xlUp).Address))


Any suggestions?

Thanks

 

[Greg Truby]

What is it that you wish to do? Perhaps?

set rngMyVar = union(Range (("B3:B" & Range("B65536").End(xlUp).row)), _
(("Y3:Y" & Range("Y65536").End(xlUp).row))

 

[Legacy 68668]

or

Range ("B3:b" & Range("B65536").End(xlUp).row & ",Y3:y" & _
     Range("Y65536").End(xlUp).row)

 

[Greg Truby]

Oh, and really, you should do it like so:

set rngWhatever = range("A1:A" & range("A" & rows.count).end(xlup).row)

in order to make your code robust going forward. The versions after Excel 2003 will have 2²⁰ rows instead of 2¹⁶.

 

[Lamar in Houston]

I am trying to copy the two columns of data. The selected cells will be pasted to another workbook which the user will have open. Note that said workbook will have a different name from day to day.

Your macro works fine on the selection but when I add a Copy statement I get the error "That command cannot be used on multiple selections". This is puzzling since I can do it on the keyboard via the Ctrl key.

Any insights?

Thanks

 

[Greg Truby]

Your macro works fine on the selection but when I add a Copy statement I get the error "That command cannot be used on multiple selections". This is puzzling since I can do it on the keyboard via the Ctrl key.
Any insights?
Thanks

You should not be able to do it interactively either (the copy that is). This would be because the size of the areas in the two columns have a differing number of rows. Try it manually. You cannot select A1:A3 and C1:C2 and copy and paste because you have differening numbers of rows. But you can select A1:A3 and C1:C3 and copy and paste, no problem.

 

[Legacy 68668]

Did you want to do like this ?

With Range ("B3", Range("B" & Rows.Count).End(xlUp))
    Range(.Address & "," & .Offset(, 23).Address).Copy
End With

 

[Lamar in Houston]

PERFECT!! This is precisely what I wanted it to do.

A very eloquently simple solution.

Thanks!

Lamar