Macro to select all the filled cells in a worksheet?

[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

 

[Claus Busch]

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

 

[GS]

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

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

 

[Victor Delta]

Hi Victor,

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


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]

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

Claus, that does the same as clicking the intersection of row/col
headers. (selects the entire sheet)<g>

 

[GS]

Another way...

Select the first cell (A1) and press Ctrl+Shift+End

 

[Claus Busch]

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

 

[GS]

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

Interesting! Thanks for clarifying...

 

[Gord Dibben]

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

 

[GS]

Gord Dibben presented the following explanation :

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

Elsewise a macro

Nice approach, Gord!

 

[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]

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

Many thanks too.

V

 

[Gord Dibben]

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]

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

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]

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.

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:


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]

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

 

[Claus Busch]

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?

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

 

[Victor Delta]

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

 

[Victor Delta]

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.

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