Delete blank worksheets

[bijan]

Hi All,
I searched to find a VBA code to detect blank worksheets and delete them, I
find one with green tick mark , but It dosen't work in my macro:
sub delwrksheet()
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
end sub
Thanks in advance
Bijan

 

[Roger Govier]

Hi

You are looking at the same sheet each time (Activesheet)
Try changing the code to to look at sh.UsedRange instaed of
ActiveSheet.UsedRange

sub delwrksheet()
Dim sh As Worksheet
Application.DisplayAlerts = False
For Each sh In ActiveWorkbook.Worksheets
If ActiveWorkbook.Worksheets.Count > 1 Then
If IsEmpty(sh.UsedRange) Then
sh.Delete
End If
End If
Next sh
Application.DisplayAlerts = True
End Sub