Select non-contiguous range to the left

[Rowland Hamilton]

How can I Select everyting to the left until I run into the wall (aka column A) without knowing how many times I need to hit the left arrow to get past the spaces? ie: Copy all data, non-contiguous, to the left of selected vertical range of cells

example: If I select AB8:AB88, I want to push to A8:AB88, disregarding blanks in between). Based on this basic code:

Range(Selection, Selection.End(xlToLeft)).SelectRange(Selection, Selection.End(xlToLeft)).Select</PRE>
Reason: I have to copy everything to the left of my new item and amount columns and paste them under the existing data set, which brings me that much closer to looping these commands about 19 times.

I found this on Ozgrid and tried to adjust it to go left but didn't get it to work:

Question
Hello Tom,
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<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
<o> </o>
Answer
Tim


Dim r as Range, lastcol as Long
set r = Range("A11",Cells(rows.count,"A").end(xlup))
lastcol = cells(11,columns.count).End(xltoLeft)
set r = r.Resize(,lastcol)
r.Select
r.copy

This assumes that I can use row 11 to find the farthest used column to the right and column A to find the last used row at the bottom of the range and the range starts in cell A11. Implied is that there is no data below your data and no date to the right of your data - at least in column 1 and row 11.

--
Regards,
Tom Ogilvy

 

[p45cal]

try these 4 statements in sequence, perhaps in the immediate pane first:

set xxx=selection.offset(,-selection.column+1).resize(,selection.column)
xxx.select
set xxx=union(xxx.specialcells(xlCellTypeConstants,23),xxx.specialcells(xlCellTypeFormulas,23))
xxx.select