macros to delete hidden sheets and hidden rows

jkharmer

Board Regular
Joined
Jul 7, 2008
Messages
77
Hi there,

I have built a monster spreadsheet, with the aim for it to cover all possible scenarios at my job. It basically involves a sheet for each type of test I may do. Some days it may need 1 sheet, others it may need 10. These all get populated into a single "Report" sheet.

What I am pulling my hair out over is trying to create 2 macros, which would be run one after the other.

The first would be to delete all hidden sheets in the workbook.

The second would be to delete all hidden rows, in the "Report" sheet only, and at the same time find any ",!#REF!" occurences that may have been generated by deleting rows, and replacing them with nothing. i.e. some cells may average 10 cells, deleting some rows will introduce say 5 valid cells, and 5 which have been deleted, leaving the ",!#REF!".

Can anyone out there help me out with these 2 macros please?

Thanks, James
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
James

I'm pretty sure this can be done but have you not considered using some sort of template worksheet/workbook?

You say you have hidden sheets, why are they hidden?
 
Upvote 0
Hi Norie,

Thanks for the super quick response!

The workbook has something like 25 tabs, but only some of them need to be used at the same time. I want to hide and then delete the uones that are not being used for that specific job to save on file size. It is 27mb, saved as an Excel 2007 macro enabled file. It just takes time to open, save etc and therefore if I can delete worksheets, and also a large number of unused rows in the "Report" tab, I can get the file size down.

I already have various simple macros within the workbook to hide and reveal rows, but I just want to be able to delete these hidden ones without manually having to do it.

I hope that makes sense.

James
 
Upvote 0
The number of rows that I would be deleting could be up to 10,000 in a single work sheet, so it would be great if the macro was "quick" as well!
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top