Forum Discussion
VBA to delete rows whose cell in a column has length zero
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
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".
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 SubThat 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)
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.
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