Finding emply sheets

  • Thread starter Thread starter Will
  • Start date Start date
W

Will

Excel 2007

How can you quickly identify completely empty sheets in a workbook? I get
workbooks from people that have lots of emply sheets, and need to quickly
identify and delete these sheets?

Thanks, Will
 
Manually?

I select the worksheet and hit ctrl-end to see where the last used cell is. If
I'm taken to A1, then I check to see if that cell is used.

If A1 is not empty, then the worksheet is used.

If A1 is empty, then there's a good chance that the sheet isn't used and can be
deleted.

But this isn't a foolproof method. The sheet could be used for other purposes
(names, vba) so you would still want to be careful.

If you think that those rules are ok, you could use this kind of thing in code:

Option Explict
Sub testme01()
dim Wks as worksheet

for each wks in activeworkbook.worksheets
with wks
if .usedrange.address = "$A$1" then
if isempty(.range("a1")) then
application.displayalerts = false
on error resume next
.delete
if err.number <> 0 then
err.clear
msgbox wks.name & " was not deleted"
end if
application.displayalerts = true
end if
end if
end with
next wks
end sub
 
Back
Top