• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Loop - delete worksheet based on cell value

C

cleantki

New Member
Hi,

I work for a company that owns multiple gas stations. I have a workbook that shows the financials for each station on its own worksheet. If the station does not have any activity within the period being reported, the worksheet is blank. I would like to create a macro that loops through the workbook and deletes all worksheets that are blank.

I found some code off the internet that does this but for some reason if there are two worksheets in a row that are blank, the second worksheet does not get deleted and I end up having to run the macro multiple times until all of the blank sheets are deleted.


Below is the code I'm using. Is there a way to remedy the problem of having to rerun the code?


Sub delete()

Dim i As Integer


Application.DisplayAlerts = False

On Error Resume Next


For i = 1 To Worksheets.Count

Sheets(i).Select

If Range("A3").Value = "" Then

Sheets(i).delete

End If

Next i

Application.DisplayAlerts = True

End Sub


Thanks for your help!
 
Hi cleantki,


Welcome to the forum..


Please chenage the code to the below.. and hope it will work for you.. :)
Code: 
Sub delete()
Dim i As Integer
Application.DisplayAlerts = False
On Error Resume Next
For i = Worksheets.Count To 1 Step -1
Sheets(i).Select
If Range("A3").Value = "" Then
Sheets(i).delete
End If
Next i
Application.DisplayAlerts = True
End Sub

Regards,

Deb
 
Last edited:
The problem is with the use of the i counter. Lets say sheets 3 and 4 are blank. When you delete sheet 3, the sheet was in position #4 is not in position #3. But the code never looks at that one, it looks at the new sheet in position #4 (which would have been sheet 5). A better method would be to look at each object in a collection, like so:
Code: 
Sub delete()
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets 'Define the collection to look at
If ws.Range("A3").Value = "" Then
'As the next step is the only bit of code we are worried about
'an error occuring on, only this one should be surrounded by
'the resume next statemnet
On Error Resume Next
ws.delete
On Error GoTo 0
End If
Next
Application.DisplayAlerts = True
End Sub
 
Last edited by a moderator:
Hi
You can also try this
Code: 
Sub DeleteEmptyWorksheet()
Dim Ws As Worksheet
Application.ScreenUpdating = False
For Each Ws In ThisWorkbook.Worksheets
If Ws.Range("A3").Value = "" Then
Application.DisplayAlerts = False
If ThisWorkbook.Sheets.Count <> 1 Then Ws.Delete
Application.DisplayAlerts = True
End If
Next Ws
End Sub
Regards
 
Last edited by a moderator:
Thank you for all of the suggestions. I tried all three suggestions but only the first one from Debraj Roy worked. The other two macros that look at all worksheets in the workbook did not work and I'm not sure why. It didn't even seem like the macro ran.


Any ideas?
 
Hi Cleantki,


Thank you for your feedback..

Just to inform you, the code, which are not run, are made by CVP's (Chandoo's Valuable Person)...

I didn't find a single reason for not running any code, only except, if you are not putting the code in your Personal.XLB..

If yes then..please change..

In place of
Code: 
ThisWorkbook.Worksheets change it to ActiveWorkbook.Worksheets

It will work for you.


waiting for your feedback..


Regards,

Deb
 
Hey Guys,

I have a very similar task to perform in a workbook. In my case, its not a single cell though. If the sum of cells D55 to O55 is 0, the sheet must be deleted. What modifications are necessary to accomplish this?

Best, David
 
Try

[pre]
Code: 
Sub delete()
Dim i As Integer
Application.DisplayAlerts = False
On Error Resume Next
For i = Worksheets.Count To 1 Step -1
Sheets(i).Select
If Application.WorksheetFunction.Sum(ActiveWorkbook.Worksheets(i).Range(&#34;D55&#34;), ActiveWorkbook.Worksheets(i).Range(&#34;O55&#34;)) = 0 Then
Sheets(i).delete
End If
Next i
Application.DisplayAlerts = True
End Sub
[/pre]
 
Hi,

Hope it's ok to post on this old thread?

I have a similar issue to the OP, and have tried the solution provided by Debraj, however when I try to run the Macro (or even before then) I get a Compile error: syntax error message on the following line:

If Range(&#34;A3&#34;).Value = &#34;&#34; Then

Any suggestions why? I'm running Excel 2007 if that makes a difference.

Many thanks
 
You must log in or register to reply here.
Back
Top