Excel 2002 : How to reduce the number of unused worksheet ?

[Mr. Low]

Dear Sir,

I have a file which was open with maximum number of worksheet (255 pages) by
its default.

I have done a lot of work in that file using up about 50 pages currently,..

I wish to only have 60 pages active and the remaining 195 pages to be added
when I need them. This is to reduce the memory usage of the PC .

Is there a way to removed those unused worksheet ?

For future new files I have already default it at 10 worksheet .



Thanks

Low

 

[smw226 via OfficeKB.com]

Hi Low,

This code will look at every one of your worksheets and delete them if they
are blank. Of course, the IF criteria can be amanded to look for something
else if you like

HTH

Simon

------------------------------------------------------------------------------
-----

Sub wkbk_cleanup()
Dim wkbk As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each wkbk In ActiveWorkbook.Worksheets

'Select the worksheet
wkbk.Select

'Look for the last used cell.
ActiveCell.SpecialCells(xlLastCell).Select

'If its A1 then the sheet is blank and can be deleted.
'Amend the test as required.

If ActiveCell.Address = "$A$1" Then
wkbk.Delete
End If

Next wkbk

End Sub


------------------------------------------------------------------------------
-------

Dear Sir,

I have a file which was open with maximum number of worksheet (255 pages) by
its default.

I have done a lot of work in that file using up about 50 pages currently,..

I wish to only have 60 pages active and the remaining 195 pages to be added
when I need them. This is to reduce the memory usage of the PC .

Is there a way to removed those unused worksheet ?

For future new files I have already default it at 10 worksheet .


Thanks

Low

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200704/1

 

[smw226 via OfficeKB.com]

Forgot to mention,

As with all untested code, please run this on a copy of your live spreadsheet.


Thanks,

Simon

Hi Low,

This code will look at every one of your worksheets and delete them if they
are blank. Of course, the IF criteria can be amanded to look for something
else if you like

HTH

Simon

------------------------------------------------------------------------------
-----

Sub wkbk_cleanup()
Dim wkbk As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each wkbk In ActiveWorkbook.Worksheets

'Select the worksheet
wkbk.Select

'Look for the last used cell.
ActiveCell.SpecialCells(xlLastCell).Select

'If its A1 then the sheet is blank and can be deleted.
'Amend the test as required.

If ActiveCell.Address = "$A$1" Then
wkbk.Delete
End If

Next wkbk

End Sub

------------------------------------------------------------------------------
-------

Dear Sir,

[quoted text clipped - 13 lines]

Low

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200704/1

 

[Mike Rogers]

Mr. Low

Save you work and back it up. Select the first one you want to delete, get
over to the last one hold down "shift" hit "enter". All those sheets should
be selected (it will say "grouped" after your file name at the top). Right
click and select "delete". Make sure you have ONLY the ones you want to
delete selected. If by some chance you deleted something not intended, close
your wok without saving and start over.

Mike Rogers

 

[Mr. Low]

Hello Simon,

Many thanks for your Macro codes.

It works well and I have successfully deleted all the unused worksheets.


Kind Regards

Low Seng Kuang
Malaysia



--
A36B58K641

Hi Low,

This code will look at every one of your worksheets and delete them if they
are blank. Of course, the IF criteria can be amanded to look for something
else if you like

HTH

Simon

------------------------------------------------------------------------------
-----

Sub wkbk_cleanup()
Dim wkbk As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each wkbk In ActiveWorkbook.Worksheets

'Select the worksheet
wkbk.Select

'Look for the last used cell.
ActiveCell.SpecialCells(xlLastCell).Select

'If its A1 then the sheet is blank and can be deleted.
'Amend the test as required.

If ActiveCell.Address = "$A$1" Then
wkbk.Delete
End If

Next wkbk

End Sub


------------------------------------------------------------------------------
-------

Dear Sir,

I have a file which was open with maximum number of worksheet (255 pages) by
its default.

I have done a lot of work in that file using up about 50 pages currently,..

I wish to only have 60 pages active and the remaining 195 pages to be added
when I need them. This is to reduce the memory usage of the PC .

Is there a way to removed those unused worksheet ?

For future new files I have already default it at 10 worksheet .


Thanks

Low

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200704/1

 

[Duffer]

How do you use the code you supplied for removing workbooks? Do I have to
create a VBA script (absolutely no idea how to do that) or do I drop it into
a macro (not any smarter about those)? I appreciate any help, very to new to
code and functions.

Duffer

Hi Low,

This code will look at every one of your worksheets and delete them if they
are blank. Of course, the IF criteria can be amanded to look for something
else if you like

HTH

Simon

------------------------------------------------------------------------------
-----

Sub wkbk_cleanup()
Dim wkbk As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each wkbk In ActiveWorkbook.Worksheets

'Select the worksheet
wkbk.Select

'Look for the last used cell.
ActiveCell.SpecialCells(xlLastCell).Select

'If its A1 then the sheet is blank and can be deleted.
'Amend the test as required.

If ActiveCell.Address = "$A$1" Then
wkbk.Delete
End If

Next wkbk

End Sub


------------------------------------------------------------------------------
-------

Dear Sir,

I have a file which was open with maximum number of worksheet (255 pages) by
its default.

I have done a lot of work in that file using up about 50 pages currently,..

I wish to only have 60 pages active and the remaining 195 pages to be added
when I need them. This is to reduce the memory usage of the PC .

Is there a way to removed those unused worksheet ?

For future new files I have already default it at 10 worksheet .


Thanks

Low

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200704/1