vba code line to select to the last row of data in 3 different columns

[bh24524]

Hi, as the title says, I'm trying to work thru some code to select data in 3 different columns which will ultimately be pasted onto a separate tab. I tried first just using ".select" after the ranges to see if the code was actually grabbing the correct info before I change that line to ".copy". I want to select A5 thru the last row of data of column B, and then from L5 thru the last column of Data in that same column. So columns A, B, and L. I tried:

Range("A5:B5", Range("B5").End(xlDown) & Range("L5").End(xlDown)).Select

but am getting an error. I haven't had to use multiple non-consecutive ranges like that before in coding, so I can only try and guess at what it would be if it's actually doable. I am wondering what the codeline would be if it is or if you have to declare a variable?

 

[dreid1011]

Something like this:

Private Sub SelectRanges()
Dim bRow As Long, lRow As Long
bRow = Range("B" & Rows.Count).End(xlUp).Row
lRow = Range("L" & Rows.Count).End(xlUp).Row
Union(Range("A5:B" & bRow), Range("L5:L" & lRow)).Select
End Sub

 

[bh24524]

Something like this:

Private Sub SelectRanges()
Dim bRow As Long, lRow As Long
bRow = Range("B" & Rows.Count).End(xlUp).Row
lRow = Range("L" & Rows.Count).End(xlUp).Row
Union(Range("A5:B" & bRow), Range("L5:L" & lRow)).Select
End Sub

Thank you Dreid, I hadn't known about the "Union" segment of code. With that I was actually able to do it without declaring a variable. I ended up simply with:

Union(Range("A5:B5", Range("B5").End(xlDown)), Range("L5", Range("L5").End(xlDown))).Copy

Thanks for that piece of knowledge. I can see it being applicable in some other things I do

 

[dreid1011]

You're welcome, and happy to help.