Delete blank worksheets

B

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
 
R

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
 
Top