Delete Empty rows in multiple sheets

Question

I wrote the code below to delete empty rows in multiple sheets. It works fine for few sheets, but it takes quite some time and I had to run it few times to get the work done. Now I need to apply this to about 100 sheets. Can someone help me to modify this code for that.

Sub delete_empty_rows()

Dim ws As Worksheet
Dim x As Integer
Dim y As String

For Each ws In ThisWorkbook.Sheets
  For x = 12 To 60
    y = Cells(x, 3).Value

    If y = "" Then
      ws.Rows(x).Delete
    End If
  Next x
Next ws
End Sub

Deleting one row at a time is very slow, also you will skip every other line since they will shift up. Use a union. — Warcupine
– Warcupine, Commented Oct 20, 2020 at 13:18

Vityata · Accepted Answer · 2020-10-20 13:25:59Z

1

The bug in the code is that it misses a worksheet reference in the line y = Cells.(x, 3).Value.

It should be y = ws.Cells.(x, 3).Value.

Without the ws, the check is done towards the ActiveSheet and the actions are taken towards the looped sheet.

There are many ways to do what you need, this is one of them, deleting rows with a number in column A, between range("A1:A10"). If you loop through all the worksheets, For Each wks in ThisWorkbook.Worksheets the code will do what you need with some changes:

Sub RemoveNumbersFromColumnA()

    Dim i As Long
    Dim myCell As Range
    Dim rowsToDelete As Range

    For i = 1 To 10
        Set myCell = Worksheets(1).Cells(i, "A")
        If IsNumeric(Left(myCell, 1)) Then
            Debug.Print myCell; " adding..."
            If rowsToDelete Is Nothing Then
                Set rowsToDelete = myCell.EntireRow
            Else
                Set rowsToDelete = Union(rowsToDelete, myCell.EntireRow)
            End If
        End If
    Next i

    If Not rowsToDelete Is Nothing Then
        rowsToDelete.Delete
    End If

End Sub

From this answer - https://stackoverflow.com/a/59637372/5448626

edited Oct 20, 2020 at 13:25

answered Oct 20, 2020 at 13:22

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pᴇʜ Over a year ago

While this solves the question (+1) properly, it may be worth to mention that the "bug" is not only the missing sheet reference but also that deleting rows within a loop changes the row counting in a sheet and therefore makes the loop jumping a row after every row delete. Therefore looping backwards For x = 60 To 12 Step -1 is necessary or collecting all rows with Union and delete after the loop (as you did). Just in case anyone is wondering why.

Vityata Over a year ago

@Pᴇʜ - yup, true.

Collectives™ on Stack Overflow

Delete Empty rows in multiple sheets

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related