|
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 04:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com