Macro to select all the filled cells in a worksheet?

  • Thread starter Thread starter Victor Delta
  • Start date Start date
V

Victor Delta

I use a number of large worksheets and often want to copy them to blank
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
 
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.
Click to expand...

try: CTRL+A


Regards
Claus Busch
 
Victor Delta was thinking very hard :
I use a number of large worksheets and often want to copy them to blank
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
Click to expand...

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
 
Claus Busch said:
Hi Victor,

Am Fri, 1 Jul 2011 20:16:00 +0100 schrieb Victor Delta:


try: CTRL+A
Click to expand...

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
 
After serious thinking Claus Busch wrote :
Hi Victor,

Am Fri, 1 Jul 2011 20:16:00 +0100 schrieb Victor Delta:


try: CTRL+A


Regards
Claus Busch
Click to expand...

Claus, that does the same as clicking the intersection of row/col
headers. (selects the entire sheet)<g>
 
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>
Click to expand...

in the german version CTRL+A only selects cells with values


Regards
Claus Busch
 
Claus Busch formulated the question :
Hi Garry,

Am Fri, 01 Jul 2011 16:07:28 -0400 schrieb GS:


in the german version CTRL+A only selects cells with values


Regards
Claus Busch
Click to expand...

Interesting! Thanks for clarifying...
 
CTRL + SHIFT + End may do the trick if Excel has not over-estimated the real
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
 
Gord Dibben presented the following explanation :
CTRL + SHIFT + End may do the trick if Excel has not over-estimated the real
used range.
Click to expand...

I agree, and thought the same as I replied but felt it would be
insignificant compared to selecting the entire sheet. Particularly in
v12+! said:
Elsewise a macro
Click to expand...

Nice approach, Gord!
 
GS said:
Another way...

Select the first cell (A1) and press Ctrl+Shift+End
Click to expand...

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
 
Gord Dibben said:
CTRL + SHIFT + End may do the trick if Excel has not over-estimated the
real
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
Click to expand...

Many thanks too.

V
 
Victor

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 explained on 7/2/2011 :
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.
Click to expand...

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.
 
Claus Busch said:
Hi Garry,

Am Fri, 01 Jul 2011 16:07:28 -0400 schrieb GS:


in the german version CTRL+A only selects cells with values
Click to expand...

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
 
In 2003 version CTRL + A selects only used range.

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
 
Hi Victor,

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?
Click to expand...

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.


Regards
Claus Busch
 
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
 
GS said:
Ron Rosenfeld explained on 7/2/2011 :

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.
Click to expand...

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
 
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
 
Back
Top