PDA

View Full Version : Solved: Delete Worksheets according to cell value



jacksonworld
02-19-2008, 03:29 PM
Hi, I have a basic problem for anyone kind enough to help.

How do I delete all worksheets that do not have a value in say, cell A2?

Thanks

next
02-19-2008, 05:05 PM
i'm a nOob, but i think this should do the trick:

If Worksheet("name").Range("A2") Is Nothing Then
Worksheet("name").Delete
End If

jacksonworld
02-19-2008, 05:15 PM
Thanks, but that will only work for one worksheet

next
02-19-2008, 05:28 PM
My bad, i misunderstood you, fixed it:

Sub Delete_blanks()
Application.DisplayAlerts = False
Dim TabCount As Integer, i As Integer
TabCount = ActiveWorkbook.Worksheets.Count

For i = 1 To TabCount
If Worksheets(i).Range("A2") = "" Then
Worksheets(i).Delete
End If
Next i

End Sub

jacksonworld
02-19-2008, 05:58 PM
Interesting. It looked to be working, then reached an error at If Worksheets(i).Range("A2") = "" Then

next
02-19-2008, 08:29 PM
Works fine, but only with 2 sheets though :whistle: .
This one shouldn't cause any errors:

Sub Delete_blanks()
Application.DisplayAlerts = False
Dim ws As Worksheet

For Each ws In Worksheets
If ws.Range("A2") = "" Then: ws.Delete
Next ws
End Sub

jacksonworld
02-19-2008, 10:12 PM
:biglaugh:

Fantastic. It works perfectly.

Thank you very much. Cheers

Bob Phillips
02-20-2008, 01:39 AM
Works fine, but only with 2 sheets though :whistle: .


Work backwards



Sub Delete_blanks()
Application.DisplayAlerts = False
Dim TabCount As Integer, i As Integer
TabCount = ActiveWorkbook.Worksheets.Count

For i = TabCount To 1 Step -1
If Worksheets(i).Range("A2") = "" Then
Worksheets(i).Delete
End If
Next i

End Sub