PDA

View Full Version : Selecting Non contiguous Range



elmariachi10
10-14-2008, 02:10 PM
Hello All,
I run a macro that selects a range of data. my dataset has changed and currently i am trying to run the same macro on a set that has blank rows/lines inbetween the data set which causes the macro to select only till it hits the blank line and leaves the rest of the data. What changes do I have to do to select all the data points inspite of these blank lines. Following is the part that selects the range and copies it.

Range("A11").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Thanks

holshy
10-14-2008, 02:19 PM
If there is nothing below and nothing to the right of the data then you can replace the whole thing with
Range("A11", Range("A11").SpecialCells(xlLastCell)).Copy

That may end up being more than you need. Is there a different number of rows and/or columns each time?

mdmackillop
10-14-2008, 02:43 PM
Sub SelectArea()
Dim Rg As Range, Col As Long
Set Rg = Range("A11")
Col = Cells(Rg.Row, Columns.Count).End(xlToLeft).Column
Range(Rg, Cells(Rows.Count, 1).End(xlUp)).Resize(, Col).Select
End Sub