Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 505
Default Excel 2002 : How to reduce the number of unused worksheet ?

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


--
A36B58K641
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 98
Default Excel 2002 : How to reduce the number of unused worksheet ?

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


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

Mr. Low wrote:
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.a...excel/200704/1

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 98
Default Excel 2002 : How to reduce the number of unused worksheet ?

Forgot to mention,

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


Thanks,

Simon

smw226 wrote:
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.a...excel/200704/1

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 194
Default Excel 2002 : How to reduce the number of unused worksheet ?

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" wrote:

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


--
A36B58K641

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 505
Default Excel 2002 : How to reduce the number of unused worksheet ?

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


"smw226 via OfficeKB.com" wrote:

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


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

Mr. Low wrote:
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.a...excel/200704/1




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Excel 2002 : How to reduce the number of unused worksheet ?

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

"smw226 via OfficeKB.com" wrote:

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


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

Mr. Low wrote:
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.a...excel/200704/1


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2002; how to reduce the size of the save dialogue window? johnthecliff Excel Discussion (Misc queries) 1 December 14th 06 05:38 AM
deleting unused space at the end of a worksheet beckyz Excel Worksheet Functions 3 October 9th 06 08:21 PM
How do I reduce the number of lines in a spreadsheet? Philamon New Users to Excel 2 February 12th 06 10:29 PM
How do I reduce number of Rows displayed in Excel worksheet? JerryRugs Setting up and Configuration of Excel 1 November 15th 05 08:55 PM
Select unused number drbobsled Excel Worksheet Functions 1 August 26th 05 07:03 PM


All times are GMT +1. The time now is 08:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"