Need inclusive selection contraction VBA with multiple selection(was "xltoleft challenge")

I'm seeking a variation of
Range(ActiveCell, ActiveCell.End(xlToLeft)).Select (like control-shift-left arrow)
but to use when more than a single cell is selected.

To demonstrate:
In an empty sheet put a zero (or anything) in B1, B2 and D2
Go to A1
Go Control-shift-end (selecting everything through the bottom right, A1:D2)
(Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select)

Now the objective is to simulate Control-Shift-Left arrow.
If you do this with the keystroke you end up with A1:B2 selected. That's what I want.
But Range(ActiveCell, ActiveCell.End(xlToLeft)).Select only leaves A1 selected, so what works?

Maybe Range(selection, selection.End(xlToLeft)).Select
is a start in the right, um, left direction.

Please Login or Register  to view this content.

Hello Oppressed1,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Regards.

Originally Posted by Andy Pope

Please Login or Register  to view this content.

Thank you Andy, though it's not always the same as control shift arrow. If [starting with an empty sheet] you have 5 of 6 cells filled, A1,B1,B2,C1,C2 (A1:C2 except for A2) it's different. Then, I'm seeing {go to A1; Ctrl-shift-end; Ctrl-shift-left arrow resulting in 2 cells selected, vs. the code sequence above resulting in 4 cells selected.

Thanks for your response.

Here's a different method. It works for your example scenario.

Please Login or Register  to view this content.

Originally Posted by AlphaFrog

Here's a different method. It works for your example scenario.

Please Login or Register  to view this content.

That looks sweet but does not change the selection. The first command "selects all" but the second command doesn't change the selection. a1:c2 stays selected.

Please Login or Register  to view this content.

BTW I did that in the immediate window (two commands, one at a time). Then I pasted your code (with the 'With' construct) into my Sheet1 code and argggh, 1004 error, whether with the original a1:d2 selection or the latest a1:c2 example. Just to be positive, I'm pasting what I used here

Please Login or Register  to view this content.

Originally Posted by Oppressed1

Please Login or Register  to view this content.

Your "test" macro worked for me in both scenarios. What was the description of the 1004 error?

Here's yet another method that works for both scenarios.

Please Login or Register  to view this content.

Thanks so much for persisting. As to the %$@&^%!! 1004, both test and test2 give the historically inept message
Run-time error '1004':
Application-defined or object-defined error

Maybe Excel version is in play here. XL03 gives 1004s when run from subs, but not when the same thing is done in Immediate (Debug) window. So evidently a 2003 bug. Funny, this is the only thing I've seen in 10 heavy usage years that actually got fixed in 2007+ (to accompany the interface annihilation).

On XL07: like with Andy's, on the original O.P. example (A1:D2) both test and test2 reproduce control-shift-left arrow.
But on my above A1:C2 scenario, both test and test2 leave everything selected (a1:c2); Andy's leaves a1:b2 selected, and ctrl-shift-left leaves a1:a2 selected.

Were you using 03 today (your profile says 03)?

Please Login or Register  to view this content.

To save hitting page-up if you're just tuning in,
A1:D2 original scenario: zero (or anything) in B1, B2 and D2
A1:C2 second scenario: zero (or anything) in all cells but A2

Originally Posted by Oppressed1

Thanks so much for persisting. As to the %$@&^%!! 1004, both test and test2 give the historically inept message
Run-time error '1004':
Application-defined or object-defined error

Maybe Excel version is in play here. XL03 gives 1004s when run from subs, but not when the same thing is done in Immediate (Debug) window. So evidently a 2003 bug. Funny, this is the only thing I've seen in 10 heavy usage years that actually got fixed in 2007+ (to accompany the interface annihilation).

On XL07: like with Andy's, on the original O.P. example (A1:D2) both test and test2 reproduce control-shift-left arrow.
But on my above A1:C2 scenario, both test and test2 leave everything selected (a1:c2); Andy's leaves a1:b2 selected, and ctrl-shift-left leaves a1:a2 selected.

Were you using 03 today (your profile says 03)?

Please Login or Register  to view this content.

To save hitting page-up if you're just tuning in,
A1:D2 original scenario: zero (or anything) in B1, B2 and D2
A1:C2 second scenario: zero (or anything) in all cells but A2

Yes I am using Excel 2003 today.

I had misunderstood the A1:C2 scenario I guess.

Is the goal to select column A (empty or not) to the first used column with data? If not, can you better describe the commonality among all the possible scenarios?

I'll explain the objective but it will just distract from the goal which truly is to get exactly what ctrl-shift-left arrow does. I gave a simplified example of a more complex sheet it will be applied to. In that, column F (for the moment, it's column F) is partially filled, and columns G to BB are completely filled. Assuming 4 header rows, I want to select A5:F900 (if row 900 is the bottom). Starting from A5, ctrl-shift-end, ctrl-shift-left gets that. I don't want to sort column G, which Andy's solution gets.

Now there are alternative solutions, like putting a named range in column F so I can refer to its .column, and other ways to get there. I can use Andy's and resize the selection with one less column for that matter. But I just thought, I ought to be able to do this, and no one's found how to make it work with (A1:C2 all filled except for A2).


Sorry to burn people's time on this, unless like me people enjoy slaying a nagging challenge. So that anyone reading this might get something valuable, here's some good links (that get *part* of the way to emulating ctrl-shift-left et al, when more than one cell is selected):

j-walk: Working With Variable-Size Ranges
http://spreadsheetpage.com/index.php...e_size_ranges/

Chip: "last cell" tricks
http://www.cpearson.com/excel/LastCell.aspx

How to select ranges using Visual Basic for Applications (novice examples)
http://support.microsoft.com/kb/291304

Ron de Bruin: Find last row, column or last cell
http://www.rondebruin.nl/win/s9/win005.htm

Originally Posted by Oppressed1

But I just thought, I ought to be able to do this, and no one's found how to make it work with (A1:C2 all filled except for A2).

Here's yet one more alternate.

Please Login or Register  to view this content.

It selects A1:B2 in the D2 scenario.
It selects A1:A2 in the C2 scenario.

It may not act like the keyboard commands for all other scenarios. If that's the goal, I don't know how to do it.

Thanks for trying. Ctrl - [shift -] - arrow navigation is mysterious. No one has answered for decades why 2 seemingly identical sheets operate different with Ctrl-Right, such as after pasting from Access; it might involve whether cells are ZLS vs. Empty vs. Used vs. something else that is pathetically left visually ambiguous rather than be exposed to users. No worries though; I'm sure the [cough, paid] Excel developers frequent forums, instead of getting stoned or giving smug interviews to Wired, because they take so much pride in their work and contribution to society, and they'll explain how it really works. Real Soon Now!

Until that near impossibility occurs, I'll just assume that it's not replicable in VBA. I really do appreciate anyone's effort trying, especially A-Frog.