Delete blank sheet
 

How do I delete all blank sheets in a file using VBA? Thanks in advance for
any answers.

Delete blank sheet
 

Hi Linglc,

Try:

'=============
Public Sub Tester()
Dim SH As Worksheet

On Error GoTo XIT
Application.DisplayAlerts = False
For Each SH In ActiveWorkbook.Worksheets
With SH
If Application.CountA(.Cells) = 0 Then
SH.Delete
End If
End With
Next SH

XIT:
Application.DisplayAlerts = True

End Sub
'<<=============


---
Regards,
Norman


"linglc" wrote in message
...
How do I delete all blank sheets in a file using VBA? Thanks in advance
for
any answers.

Delete blank sheet
 

Try this

Dim sh As Worksheet

Application.DisplayAlerts = False
For Each sh In ActiveWorkbook.Worksheets
If ActiveWorkbook.Worksheets.Count 1 Then
If IsEmpty(ActiveSheet.UsedRange) Then
sh.Delete
End If
End If
Next sh
Application.DisplayAlerts = True


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"linglc" wrote in message
...
How do I delete all blank sheets in a file using VBA? Thanks in advance
for
any answers.

Delete blank sheet
 

careful you don't (try to) delete them all <vbg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Norman Jones" wrote in message
...
Hi Linglc,

Try:

'=============
Public Sub Tester()
Dim SH As Worksheet

On Error GoTo XIT
Application.DisplayAlerts = False
For Each SH In ActiveWorkbook.Worksheets
With SH
If Application.CountA(.Cells) = 0 Then
SH.Delete
End If
End With
Next SH

XIT:
Application.DisplayAlerts = True

End Sub
'<<=============


---
Regards,
Norman


"linglc" wrote in message
...
How do I delete all blank sheets in a file using VBA? Thanks in advance
for
any answers.

Delete blank sheet
 

Hi Bob,

careful you don't (try to) delete them all <vbg

Try my code on an emty workbook! <ebg


---
Regards,
Norman

Delete blank sheet
 

Hi Bob,

Even an emPty one!


---
Regards,
Norman

Delete blank sheet
 

Hi Norman,

I missed your error handler <vbg. I should have done it that way (doh!)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Norman Jones" wrote in message
...
Hi Bob,

Even an emPty one!


---
Regards,
Norman