finding a blank sheet

[cparsons]

Does anybody have an idea of the best way to look in a workbook an
delete any sheet that is completely blank? Therefore only keepin
worksheets that have data in them.

Thanks for your help,
Crai

 

[SOS]

cparsons,

I don't profess to have written this but have found the folowing cod
which would appear to do what you want

Sub Delete_EmptySheets()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If Application.WorksheetFunction.CountA(sh.Cells) = 0 Then
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End If
Next
End Sub

Regards

Seamu

 

[Jazzer]

Hi,

This VBA macro does the trick:

Sub RemoveEmptyWS()
Dim ws As Object

Application.DisplayAlerts = False

For Each ws In Worksheets
If ws.Cells.SpecialCells(xlCellTypeLastCell).Address = "$A$1
Then
ws.Delete
End If
Next ws

Application.DisplayAlerts = True

End Sub

You can remove (or comment) the "Application.DisplayAlerts = ...
lines, if you want Excel to confirm the sheet deletions.

- Asse

 

[cparsons]

That was it. Thanks for your help.

Crai

 

[cparsons]

Thanks for both your ideas. Both your posts are similar and work but is
there any advantage of defining an object over a worksheet?


Thanks,
Craig

 

[Dave Peterson]

One of the big advantages of using the correct type when you define an object
variable is the intellisense help that you get from the VBE.

If you do
dim ws as worksheet

and later (while you're coding), type ws. (W-S-(dot)), you'll see all of the
properties and methods that apply to a worksheet. You can choose from this list
to save typing time (and more importantly, typing mistakes!).

Be a little careful with Jazzer's version--especially if you have a worksheet
with just an entry in A1.

 

[Jazzer]

*Be a little careful with Jazzer's version--especially if you have
worksheet with just an entry in A1.
Dave Peterson
(e-mail address removed) *

Yeah. I notised the problem after I left my from my computer yesterday
If something is in cell A1 and nothing else on your worksheet, th
worksheet will be deleted.

- Asse