VBA to delete rows whose cell in a column has length zero

[Antonino2023]

Hello,

I'm running into an issue on a task that seems fairly simple, but is stumping me nonetheless. It is as follows:

In columns A-C I have an Excel Table with some rows (A7, A8, A11, A12) that are not truly empty (IsEmpty() will not return True on them). Instead, their cell value is ="". That is the result of the cell formula = IF(D2 = 1, "", "Error"). What I want to accomplish is the table shown on the right (columns E-G). I have tried the following code:

Sub TestDeleteRow()
Dim wb As Workbook
Dim ws As Worksheet
Dim tblTest As ListObject
Dim ChildNumColumn As Range
Dim TotRows As Long
Dim cell As Range

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Test")
Set tblTest = ws.ListObjects("Test_T")

' Define the table column Data Body Range where we will be checking if the cell's length is 0
Set ChildNumColumn = tblTest.ListColumns("Child #").DataBodyRange

For Each cell In ChildNumColumn.Cells
    If Len(cell.Value) = 0 Then
    cell.EntireRow.Delete
    End If
Next cell

End Sub

 It does not appear to work since the following error is thrown:

In debugging, the error comes from line 18 of the code posted above. 

Does anyone have suggestions for achieving my task, or pointers to where my error is coming from?

[#2]

@Antonino2023 

The runtime error 1004 occurs because you are modifying the range while iterating through it in the loop. Deleting rows inside the loop changes the number of rows in the range, causing the runtime error.

To overcome this issue, you can loop through the cells in reverse order, starting from the last cell and moving upwards. Deleting rows from the bottom up ensures that the row deletions do not affect the loop.

Here's an updated version of your code that uses a reverse loop to delete rows with empty cells in the "Child #" column:

Sub TestDeleteRow()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim tblTest As ListObject
    Dim ChildNumColumn As Range
    Dim LastRow As Long
    Dim i As Long
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Test")
    Set tblTest = ws.ListObjects("Test_T")
    
    ' Define the table column Data Body Range where we will be checking if the cell's length is 0
    Set ChildNumColumn = tblTest.ListColumns("Child #").DataBodyRange
    
    LastRow = ChildNumColumn.Rows.Count
    
    For i = LastRow To 1 Step -1
        If Len(ChildNumColumn.Cells(i).Value) = 0 Then
            ChildNumColumn.Cells(i).EntireRow.Delete
        End If
    Next i
End Sub

By iterating through the cells in reverse order, the loop won't be affected by the row deletions, and it will correctly delete the rows where the cell in the "Child #" column has a length of zero.

[#3]

@NikolinoDE 

Thank you for the explanation! The code changes make sense, but I used your code and received the same error "Delete method of Range class failed".

[#4 · Accepted solution]

@Antonino2023 

How about

Sub TestDeleteRow()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim tblTest As ListObject
    Dim ChildNumColumn As Range
    Dim LastRow As Long
    Dim i As Long
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Test")
    Set tblTest = ws.ListObjects("Test_T")
    
    ' Define the table column Data Body Range where we will be checking if the cell's length is 0
    Set ChildNumColumn = tblTest.ListColumns("Child #").DataBodyRange
    
    LastRow = ChildNumColumn.Rows.Count
    
    For i = LastRow To 1 Step -1
        If Len(ChildNumColumn.Cells(i).Value) = 0 Then
            tblTest.ListRows(i).Delete
        End If
    Next i
End Sub

[#5]

@Hans Vogelaar 

That worked!

Would the source of the prior error be that ChildNumColumn is referencing ListColumns("Child #"), so when I was trying to delete a row in the loop, it was running into an issue where it could not delete the cell in column "Child #" without affecting the other cells in that row? (In the sense that I was trying to delete the entire row in ONLY column "Child #" as opposed to the entire row of the table itself)

[#6]

@Antonino2023 

You were trying to delete the entire row from the sheet (from column A to column XFD).

This can cause problems, for example if there are other tables on the same sheet.

The version that I posted deletes only the table row.

[#7]

@Hans Vogelaar 

I implemented that code on that dummy table, but when I went to implement it in my real scenario, it did not do actually delete rows (it did nothing, no error thrown). I attached a screen recording of me debugging the Sub. As you will see, the For loop breaks out after only 1 iteration, even though LastRow is 50 (correct value). Would you happen to know why it did not go to 49 and so forth?

P.S. I am using the delete row sub in another sub

[#8]

@Antonino2023 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.

[#9]

@Hans Vogelaar 

I figured it out!

Just as I was about to send a copy, I figured I would try one more thing:

I replaced tbl.ListRows(i).Delete with tbl.DataBodyRange.Rows(i).Delete inside that for loop. Seems that that the loop did not like ListRows(). Thank you Hans and @NikolinoDE for your help on this issue!