Question:

I have written a short code to call a macro to run it on a few worksheets one after another. This macro deletes all the blank rows in all the worksheets in which it runs.
Now the problem is that if not all the worksheets contain useful data so I would like to delete a few worksheets before running the macro to reduce the run time. But if I delete any of the worksheets the macro stops working and returns an error.
For eg. Say the “AUD” tab (worksheet) doesnt contain any data and I want to delete it. I want the macro to immediately move from “EUR” tab to “GBP” tab if the “AUD” tab is deleted. Similarly, say if “AUD” and “GBP” is deleted then it should move directly to “JPY” from “EUR”.

Any help or workaround shall be appreciated.
Code Snippet:

Sub delRWS()
Sheets(“USD”).Activate
delt
Sheets(“EUR”).Activate
delt
Sheets(“AUD”).Activate
delt
Sheets(“GBP”).Activate
delt
Sheets(“JPY”).Activate
delt
Sheets(“Cover Sheet”).Activate
MsgBox (“Done!”)
End Sub
Sub delt()
For i = 21 To 1000
If Cells(i, 14).Text = “” Then
Rows(i).Select
Selection.Delete Shift:=xlUp
If Cells(i + 2, 7).Text = “Subtotal Rec Items” Then
GoTo Over
End If
i = i – 1
End If
Next i
Over:
End Sub

Solution:

Apologies … I left a test line in place – deleted below.

Chris

b delRWS()
On Error Resume Next
delt Sheets(“USD”)
delt Sheets(“EUR”)
delt Sheets(“AUD”)
delt Sheets(“GBP”)
delt Sheets(“JPY”)
delt Sheets(“Cover Sheet”)
MsgBox (“Done!”)
End Sub
Sub delt(ws As Worksheet)

For i = 21 To 1000
If ws.Cells(i, 14).Text = “” Then
ws.Rows(i).Select
ws.Application.Selection.Delete Shift:=xlUp
If ws.Cells(i + 2, 7).Text = “Subtotal Rec Items” Then
GoTo Over
End If
i = i – 1
End If
Next i
Over:
End Sub