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

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, ...

BI & Data Analysis

excel

Formulas and Functions

Macros and VBA

HansVogelaar

May 26, 2023

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

HansVogelaar

MVP

May 26, 2023

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.

Antonino2023

Brass Contributor

May 26, 2023

HansVogelaar

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!

Forum Discussion

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