Selection multiple ranges with Cells property

[carmjo005]

How can I select multiple non-adjacent ranges using the Cells property.
I know that Range("A2:A7, F2:F7, J2:J7").Select works. However the number of
rows varies each time the macro is run. Also, although the number of rows may
vary each time the macro is run the number of rows in A, F, and J
respectively, remain equal with each run. I've tried the following

' I = last data row
Dim R1, R2, R3, MyMultipleRange As Range
Sheets("Scratch").Select
R1=Range(Cells(2, 1), Cells(I, 1))
R2=Range(Cells(2, 6), Cells(I, 6))
R3=Range(Cells(2, 10), Cells(I, 10))
Set myMultipleRange = Union(R1, R2 R3)
myMultipleRange.Select

with no success. I plan to use the selected range in a ListBox and have been
able to do this if I make the three columns of data adjacent. But I thought
I'd ask for expert help first. Can it be done without making the columns
adjacent? Or, is there a better way?
Thanks

 

[Barb Reinhardt]

Try this

Dim lRow as long
Dim aWS as WOrksheet

set aWS = ActiveSheet

'Finds the last row in column 1

lRow = aWS.cells(aws.rows.count,1).end(xlup).row


HTH,
Barb Reinhardt

 

[carmjo005]

Thank you Barb, but determining the last data row in each data column is not
the problem. The problem is that I have not been able to figure out how to
..Select three or more non-adjacent ranges (eg A2:A7, F2:F7, J2:J7) and use
this data for display in a ListBox. Once/if the area is selected I have used
ListBox1.RowSource = ActiveWindow.RangeSelection.Address. And then used this
as the SourceRange for my ListBox. But all my code variations/attempts have
thusfar failed. I can move F2:F7 to B2:B7 and J2:J7 to C2:C7 and then use
Range(Cells(2, 1), Cells(I, 3)) as my SourceRange (where I = 7 in this
example) but if I can accomplish the same thing without the move - on non
adjacent ranges I'd love to know how.
Also, on first post, Typos are MyMultipleRange should be myMultipleRange and
Union(R1, R2 R3) should be Union(R1, R2, R3).
Thanks Again

 

[Barb Reinhardt]

You also should change this

R1=Range(Cells(2, 1), Cells(I, 1))
R2=Range(Cells(2, 6), Cells(I, 6))
R3=Range(Cells(2, 10), Cells(I, 10))

To

Set R1=Range(Cells(2, 1), Cells(I, 1))
Set R2=Range(Cells(2, 6), Cells(I, 6))
Set R3=Range(Cells(2, 10), Cells(I, 10))

These problems could be mitigated if you use 'Option Explicit' at the
beginning of the module.

HTH,
Barb Reinhardt

 

[carmjo005]

Thanks again Barb. The changes you suggested worked perfectly, but then I ran
into difficulty getting a ListBox to accept the Selected non-adjacent
multi-range Union. If you have a source that may help me understand this area
- I'd greatly appreciate it. In the mean time, I went back to my old way
(move data to a scratch area making data columns adjacent) - and it works
fine. Thank you for your expertice.
Regards