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

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.