Macro to select all the filled cells in a worksheet?

[Victor Delta]

Gord

Many thanks for nailing this so comprehensively! It never ceases to amaze me
how, despite working with Excel for about 20 years, one always keeps
learning new things!

V

 

[GS]

Victor Delta submitted this idea :

Garry

When I do this (...hit the Up or Left Arrow key while holding down
Ctrl+Shift), it just takes the cells selected back to the row 1 or column
A...? Are you saying it should give the currently filled cells rectangle?

V

I'm saying that IF Ctrl+Shift+End selects cells outside the data area
(ie: empty cells) then using the arrow keys while still holding down
Ctrl+Shift will deselect those empty cell columns/rows. Thus, if
Ctrl+Shift+End only selects non-empty columns/rows then there's nothing
to do. IOW, only use the arrow keys if empty columns/rows are selected!

 

[GS]

Victor Delta wrote :

Gord

Many thanks. I see what you mean, although curiously after a few attempts,
Excel (I'm using XP/2002) seems to forget the previously filled cells and the
macro selects just the currently filled cells. Presumably there's a good
reason for this behaviour...?

Anyway, I've changed over to using your macro which provides a much more
robust and reliable solution. Thanks again.

V

If you delete columns/rows that used to contain data, saving the file
resets the used range to exclude those deleted column/rows IF they were
outside the non-empty columns/rows area.

 

[GS]

I should also state that Ctrl+Shift+End WILL INCLUDE cells that contain
formulas which return an empty string ("")! So while these may 'appear'
empty, they're not empty because they contain formulas.

 

[Victor Delta]

Victor Delta submitted this idea :

I'm saying that IF Ctrl+Shift+End selects cells outside the data area (ie:
empty cells) then using the arrow keys while still holding down Ctrl+Shift
will deselect those empty cell columns/rows. Thus, if Ctrl+Shift+End only
selects non-empty columns/rows then there's nothing to do. IOW, only use
the arrow keys if empty columns/rows are selected!

Thanks. I wonder why this doesn't work for me using Excel XP/2002?

V

 

[Victor Delta]

Victor Delta wrote :

If you delete columns/rows that used to contain data, saving the file
resets the used range to exclude those deleted column/rows IF they were
outside the non-empty columns/rows area.

Thanks. I didn't save the file - I hadn't even given it a name - but perhaps
the autosave has the same effect?

V

 

[Gord Dibben]

Thanks. I wonder why this doesn't work for me using Excel XP/2002?

V

Depends where you start.

Try this on a new sheet.

Enter any data in A1:F20

CTRL + SHIFT + End selects just those cells.

Now select A11:F20 and "clear contents"

Select A1 and ctrl + shift + end which selects A1:F20

With ctrl + shift held, hit uparrow key................now should have A1:F10
selected..

One more twist.

Clear contents of just A2:A10

Select A1

ctrl + shift + end then hit uparrow

Your selection is now A1:F1, not A1:F10

Good stuff, eh!


Gord

 

[Gord Dibben]

Victor

Excel 2002(XP) does not have an "autosave".

It has autorecovery which does not save or overwrite the original file.

Just makes a temporary copy in background in case Excel crashes.

The temporary copy deletes itself when Excel has finished working with it.


Gord

 

[GS]

It happens that Victor Delta formulated :

Thanks. I wonder why this doesn't work for me using Excel XP/2002?

V

Not sure myself since I tested it using XL2002 on XP (my default
instance). It works fine for me. However, Gord's exercise is worth
spending time to do because it reveals the behavior of the arrow keys
combined with Ctrl+Shift. This is good stuff to know because it gives
good hints about how, exactly, to use (or not use) this keyboard
combination.

Just to add to Gord's exercise...

Select F1, do Ctrl+Shift+End, then hit the Left arrow key while holding
down Ctrl+Shift.

Another exercise:

Select F10, press Ctrl+Shift+Home.

Doing Gord's exercise followed with the above 2 exercises should give
you a really good working knowledge of how to work with keyboard
combinations in various ways so you can service a variety of needs.

 

[Victor Delta]

Gord

Sorry to tell you that, using Excel XP, when I get to the line half way down
'With ctrl + shift held, hit up-arrow key................now should have
A1:F10 selected..' I am actually left with only A1:F1 selected...?

V

 

[GS]

Victor Delta was thinking very hard :

Sorry to tell you that, using Excel XP, when I get to the line half way down
'With ctrl + shift held, hit up-arrow key................now should have
A1:F10 selected..' I am actually left with only A1:F1 selected...?

Not meaning to nitpick on anyone.., but Gord's example exercise worked
exactly as written when I tried it.

 

[Victor Delta]

And to my amazement I found exactly the same when using Excel 2003 today!

I must be doing something wrong - even though I have following your
instructions to the letter!

V

 

[Rick Rothstein]

Another method...

1. Edit/Find on menu bar or, alternatively, press Ctrl+F
2. Type an asterisk (*) in the "Find what" field
3. Click "Find All" button
4. Press Ctrl+A
5. Click "Close" button

Rick Rothstein (MVP - Excel)

 

[Clif McIrvin]

My guess is that you have some empty cells inside that A1:F10 range ...

(Which would alter the results from Gord's exercise.)

--Clif

 

[Victor Delta]

Genius! You're absolutely right, I had not filled every cell as often in my
spreadsheets there are empty cells within the generally populated rectangle
of cells (e.g. comment cells etc) and so it had never occurred to me that
every cell needed to be filled for the shortcuts to work.

I'll just stick to Gord's macro for both Excel XP and 2003!

V

 

[Rick Rothstein]

I'll just stick to Gord's macro for both Excel XP and 2003!

If I correctly understand what you want, then here is a one-liner (albeit a
long one) non-looping macro that I think does the same thing...

Sub PickedActualUsedRange()
Range("A1").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select
End Sub

Rick Rothstein (MVP - Excel)

 

[Rick Rothstein]

That first line was supposed to be a "quoted section" from the message I was
answering. In other words, it should have looked like this...

I'll just stick to Gord's macro for both Excel XP and 2003!

but I forgot the greater than symbol. Sorry for any confusion.

Rick Rothstein (MVP - Excel)

 

[Clif McIrvin]

I'll just stick to Gord's macro for both Excel XP and 2003!

If I correctly understand what you want, then here is a one-liner
(albeit a long one) non-looping macro that I think does the same
thing...

Sub PickedActualUsedRange()
Range("A1").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select
End Sub

Rick Rothstein (MVP - Excel)

Rick - Once again -- Thank You! for sharing your knowledge of things
Excel here!

Much appreciated!!!

Victor Delta: Not so long ago I'd have looked [blankly!] at Rick's
macro and gone away wondering just waht it was his code was doing.

Then, I spent some time picking some of his code apart and learning what
it does .... time well spent, I assure you! <grin>

 

[Rick Rothstein]

Rick - Once again -- Thank You! for sharing your knowledge

of things Excel here!

Much appreciated!!!

Victor Delta: Not so long ago I'd have looked [blankly!] at
Rick's macro and gone away wondering just waht it was
his code was doing.

Then, I spent some time picking some of his code apart and
learning what it does .... time well spent, I assure you! <grin>

Hey Clif, thanks for the kind words... they are "much appreciated" as well.

I can't tell you how happy your last sentence above makes me. I know I tend
to write obfuscated (sometimes maybe even verging on "clever") code... so I
am always glad when someone tells me they have taken the time to dissect
what I have written in order to understand how it works because, in doing
so, they are telling me that they really wants to learn how to control Excel
better... and it makes me glad to know my code is being used as a learning
tool in that effort.

Just a follow up on the macro I posted. As written, it identifies a range
encompassing all cells that are displaying a value, whether that value is a
typed in constant or the result of a formula... the xlValues assignment to
the LookIn argument inside of each Find function is doing that... however,
there may be times when you need to identify all cells that
contains"anything", even formulas that are displaying the empty string. To
do that, just change the xlValues to xlFormulas.

Rick Rothstein (MVP - Excel)

 

[GS]

Rick Rothstein was thinking very hard :

I'll just stick to Gord's macro for both Excel XP and 2003!

If I correctly understand what you want, then here is a one-liner (albeit a
long one) non-looping macro that I think does the same thing...

Sub PickedActualUsedRange()
Range("A1").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select
End Sub

Rick Rothstein (MVP - Excel)

Rick,
Absolutely brilliant! I too share Clif's sentiments to you regarding
your contributions, and how much they have helped me understand how to
assemble some of my own. Most inspiring to me is how your stuff MAKES
me think and learn. I'll be a committed fan forever...