Macro to select all the filled cells in a worksheet?
Victor Delta
worksheets. To do this, it is very tempting to click the button at the top
left hand corner of the chosen sheet to select the whole sheet, before
copying and pasting to the blank sheet. However, this results in all the
blank cells being copied too, which leads to a number of problems.
I am therefore looking for a quick automatic way to select just the
rectangle of cells from A1 to the most distant filled cell.
Does anyone please know of a tool or macro that will do this (and possibly
copy the cells too)?
Thanks,
V
Claus Busch
Am Fri, 1 Jul 2011 20:16:00 +0100 schrieb Victor Delta:
> I am therefore looking for a quick automatic way to select just the
> rectangle of cells from A1 to the most distant filled cell.
try: CTRL+A
Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
GS
Try running this macro. You could store it in PERSONAL.XLS for future
use whenever you need it.
Sub Select_UsedRange()
ActiveSheet.UsedRange.Select
End Sub
--
Garry
Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
GS
> Hi Victor,
>
> Am Fri, 1 Jul 2011 20:16:00 +0100 schrieb Victor Delta:
>
>> I am therefore looking for a quick automatic way to select just the
>> rectangle of cells from A1 to the most distant filled cell.
>
> try: CTRL+A
>
>
> Regards
> Claus Busch
Claus, that does the same as clicking the intersection of row/col
headers. (selects the entire sheet)<g>
Victor Delta
news:iul6ro$9d3$1...@news.albasani.net...
> Hi Victor,
>
> Am Fri, 1 Jul 2011 20:16:00 +0100 schrieb Victor Delta:
>
>> I am therefore looking for a quick automatic way to select just the
>> rectangle of cells from A1 to the most distant filled cell.
>
> try: CTRL+A
Claus
Thanks, but Ctrl + A is 'Select All' (i.e. both used and empty cells) and
results in the same problem that I outlined in my first paragraph.
What I'm looking for is a way of selecting only the rectangle of filled
cells.
V
GS
Select the first cell (A1) and press Ctrl+Shift+End
Claus Busch
Am Fri, 01 Jul 2011 16:07:28 -0400 schrieb GS:
> Claus, that does the same as clicking the intersection of row/col
> headers. (selects the entire sheet)<g>
in the german version CTRL+A only selects cells with values
GS
> Hi Garry,
>
> Am Fri, 01 Jul 2011 16:07:28 -0400 schrieb GS:
>
>> Claus, that does the same as clicking the intersection of row/col
>> headers. (selects the entire sheet)<g>
>
> in the german version CTRL+A only selects cells with values
>
>
> Regards
> Claus Busch
Interesting! Thanks for clarifying...
Gord Dibben
used range.
Elsewise a macro
Sub UsedRangePick()
Dim tempRange As Range
Set tempRange = RangeToUse(ActiveSheet)
tempRange.Select
End Sub
Function RangeToUse(anySheet As Worksheet) As Range
Dim i As Integer, c As Integer, R As Integer
With anySheet.UsedRange
i = .Cells(.Cells.Count).Column + 1
For c = i To 1 Step -1
If Application.CountA(anySheet.Columns(c)) > 0 _
Then Exit For
Next
i = .Cells(.Cells.Count).Row + 1
For R = i To 1 Step -1
If Application.CountA(anySheet.Rows(R)) > 0 Then _
Exit For
Next
End With
With anySheet
Set RangeToUse = .Range(.Cells(1, 1), .Cells(R, c))
End With
End Function
Gord Dibben MS Excel MVP
GS
> CTRL + SHIFT + End may do the trick if Excel has not over-estimated the real
> used range.
I agree, and thought the same as I replied but felt it would be
insignificant compared to selecting the entire sheet. Particularly in
v12+!<g>
>
> Elsewise a macro
Nice approach, Gord!
>
> Sub UsedRangePick()
> Dim tempRange As Range
> Set tempRange = RangeToUse(ActiveSheet)
> tempRange.Select
> End Sub
>
> Function RangeToUse(anySheet As Worksheet) As Range
> Dim i As Integer, c As Integer, R As Integer
>
> With anySheet.UsedRange
> i = .Cells(.Cells.Count).Column + 1
> For c = i To 1 Step -1
> If Application.CountA(anySheet.Columns(c)) > 0 _
> Then Exit For
> Next
> i = .Cells(.Cells.Count).Row + 1
> For R = i To 1 Step -1
> If Application.CountA(anySheet.Rows(R)) > 0 Then _
> Exit For
> Next
> End With
>
> With anySheet
> Set RangeToUse = .Range(.Cells(1, 1), .Cells(R, c))
> End With
> End Function
>
>
> Gord Dibben MS Excel MVP
--
Victor Delta
> Another way...
>
> Select the first cell (A1) and press Ctrl+Shift+End
Garry
Many thanks for your various suggestions. Putting them together, I recorded
a macro which came out as:
Sub Select_Filled_Cells()
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
End Sub
and this seems to do the job perfectly.
Thanks again.
V
Victor Delta
news:cvbs0714ufvupf4ta...@4ax.com...
Many thanks too.
V
Gord Dibben
Be careful with that one.
Only good if you have reset the used range by code or by deleting all empty rows
and columns them saving.
Example....................go to IV50000 and enter some text.
Now "clear contents" of that cell.
Run the macro and see what Excel thinks is the used range.
Gord
Ron Rosenfeld
To do this without a macro, to select the used range:
Select A1
<ctrl><shift><end> (Hold down <ctrl><shift> and then hit <end>)
This has the advantage of being quick and simple.
This has the disadvantage of possibly selected cells outside the used range if you have entered and subsequently deleted data.
GS
I agree, and already suggested this as an alternative to using a macro.
However, IMO it's equally as easy to hit the Up or Left Arrow key while
holding down Ctrl+Shift to eliminate cells outside the 'real' used
range.
Victor Delta
> Hi Garry,
>
> Am Fri, 01 Jul 2011 16:07:28 -0400 schrieb GS:
>
>> Claus, that does the same as clicking the intersection of row/col
>> headers. (selects the entire sheet)<g>
>
> in the german version CTRL+A only selects cells with values
That's really interesting, and begs the question why the German version of
Excel should be different in this way?
Having previously assumed all the international versions of Excel were the
same in terms of basic functionality - but with obvious language
differences - I wonder if other versions have similar small differences in
terms of shortcuts etc?
V
Gord Dibben
CTRL + A(twice) selects all cells.
97 and 2002 versions CTRL + A selected all cells.
2003 version was only one I remember with the (twice) feature.
Gord Dibben MS Excel MVP
Claus Busch
Am Sat, 2 Jul 2011 18:19:23 +0100 schrieb Victor Delta:
> Having previously assumed all the international versions of Excel were the
> same in terms of basic functionality - but with obvious language
> differences - I wonder if other versions have similar small differences in
> terms of shortcuts etc?
I also thought that shortcuts are international.
In german language version CTRL+A will select all cells (entire sheet)
if worksheet is empty. With values in the sheet, CRTL+A only selects the
used range. Also used range will be selected with CRTL+Shift+blank and
CRTL+Shift+End.
Victor Delta
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
"Gord Dibben" <phno...@shaw.ca> wrote in message
news:454t07hp9i2p2rptq...@4ax.com...
Victor Delta
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
Victor Delta
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
"Gord Dibben" <phno...@shaw.ca> wrote in message
news:uimu07hff11hp3u3p...@4ax.com...
GS
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
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
formulas which return an empty string ("")! So while these may 'appear'
empty, they're not empty because they contain formulas.
Victor Delta
Thanks. I wonder why this doesn't work for me using Excel XP/2002?
V
Victor Delta
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
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
> 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
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
"Gord Dibben" <phno...@shaw.ca> wrote in message
news:g9bv07deppggaa4ml...@4ax.com...
GS
> 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
I must be doing something wrong - even though I have following your
instructions to the letter!
V
"Victor Delta" <no...@nospam.com> wrote in message
news:iuqkjh$1mm$1...@dont-email.me...
Rick Rothstein
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
(Which would alter the results from Gord's exercise.)
--Clif
"Victor Delta" <no...@nospam.com> wrote in message
news:iut930$itk$1...@dont-email.me...
--
Clif McIrvin
(clare reads his mail with moe, nomail feeds the bit bucket :-)
Victor Delta
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
"Clif McIrvin" <clare....@gmail.com> wrote in message
news:iutchq$a5j$1...@dont-email.me...
Rick Rothstein
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
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.
Clif McIrvin
news:iutk5i$o8a$1...@dont-email.me...
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
> 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.
GS
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...
Rick Rothstein
> 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...
Wow! Thank you for your nice comments. I am speechless. All I can say is
thank you so much. I am glad that you (and Clif and hopefully others) are
finding my postings to be helpful in your own VB coding efforts.
GS
Well, I can't tell you exactly how many lines of comment in my projects
sasy this...
'by Rick Rothstein
but it's quite a few. You truly inspire me towards bettering my
programming skills however I can! Thanks so much for that
inspiration...
Victor Delta
news:iutk5i$o8a$1...@dont-email.me...
Rick
Very many thanks. Just back after a couple of days away so will give it a
run tomorrow.
Thanks again,
V
Victor Delta
Perfect!
Many thanks again,
V