Select non-contiguous range to the left

Rowland Hamilton

Active Member
Joined
Nov 13, 2009
Messages
250
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-com:eek:ffice:eek:ffice" /><o:p></o:p>

<o:p> </o:p>
<o:p> </o:p>
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
try these 4 statements in sequence, perhaps in the immediate pane first:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,962
Messages
5,599,065
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top