Deleting Sheets With One Row Excel VBA

Question

I am creating some sheets and would like to delete the ones with only one row. I have tried following two codes but they did not work.

WSCount = Worksheets.Count
For l = 1 To WSCount
Worksheets(l).Activate
If IsEmpty(ActiveSheet.Cells(2, 1)) Then
ActiveSheet.Delete
WSCount = WSCount - 1
l = l - 1
End If
Next l

Below is the second one.

For Each Worksheet in ActiveWorkbook.Worksheets
If IsEmpty(ActiveSheet.Cells(2,1)) Then
ActiveSheet.Delete
End If
Next

The problem I am encountering is when I delete pages, it messes with the for loop. This directly happens at the first code. In the second code, the problem is that I am not activating that sheet so excel does not delete it. I have to put a for loop in that one too, which makes me encounter the same problem at the first code.

There is probably a simple solution but my knowledge is limited so all I could think is putting a for loop.

in the second replace both `ActiveSheet` with `Worksheet` – Scott Craner Feb 04 '19 at 15:04 — Scott Craner, Feb 04 '19 at 15:04

SJR · Accepted Answer · 2019-02-04T15:14:51.130

4

That's why we always loop backwards when deleting sheets, rows, columns ...

Sub x()

Dim WSCount As Long, l As Long

WSCount = Worksheets.Count

For l = WSCount To 1 Step -1
    If IsEmpty(Worksheets(l).Cells(2, 1)) Then
        Worksheets(l).Delete
    End If
Next l

End Sub

As Scott says, your second bit of would work thus as in your existing code the activesheet never changes so you would only ever delete a single sheet.

For Each Worksheet in ActiveWorkbook.Worksheets
  If IsEmpty(worksheet.Cells(2,1)) Then
    worksheet.Delete
   End If
Next

Also read up on how to avoid select.

edited Feb 04 '19 at 15:14

answered Feb 04 '19 at 15:08

SJR

22,986
6
18
26

Out of curiosity, is there a more-accurate way of checking to see if only the first row is used? As it stands, this could delete worksheets that have data on more than the first row, but have an empty cell at A2. – DukeSilver Feb 04 '19 at 15:56
1

@DukeSilver - that amounts to finding the last used row for which there are various methods. Find is perhaps the most robust. Could use UsedRange, but not 100% reliable, or End(xlup) if you know there will be data in a specific column. – SJR Feb 04 '19 at 16:06

score 1 · Answer 2 · answered Feb 04 '19 at 16:13

1

Sub deleteSheet()
Application.DisplayAlerts = False
For Each sh In ThisWorkbook.Worksheets
    If IsEmpty(sh.Cells(2, 1).Value) Then
        sh.Delete
    End If
Next
Application.DisplayAlerts = True
End Sub

answered Feb 04 '19 at 16:13

Gustavo Queiroz

33
5

Deleting Sheets With One Row Excel VBA

2 Answers2