Following a tutorial to delete empty cells in a table I keep getting a type mismatch error and I can’t understand why. From what I can see DataBodyRange returns a range but the issue seems to present itself for the For Each row In rng.Rows line.
Is this jumping out to anyone?
Dim ws As Worksheet
Dim tbl As ListObject
Dim rng As Range
Dim row As ListRow
Set ws = ThisWorkbook.Sheets("RawIPs")
For Each tbl In ws.ListObjects
Set rng = tbl.DataBodyRange
For Each row In rng.Rows
If Application.WorksheetFunction.CountA(row) = 0 Then
row.Delete
End If
Next row
Next tbl
3
Delete Blank Rows
- I guess you’re a victim of bad AI code. This looks exactly like what ChatGPT would answer minus the comments. AI-generated code is banned from SO i.e. it can be used in questions but not answers.
- I found almost the same code here where the answerer even confirms it: “The text, the code and steps were edited with the help of AI.”.
The Issues
- A range doesn’t have a
ListRowsproperty. - When deleting single rows, you must loop backward to delete each row successfully. Otherwise, when there are two consecutive blank (empty) rows, the second one will not be deleted.
A Working Code
- Note that
COUNTBLANK, besides empty cells, also includes cells with formulas evaluating to an empty string or such cells copied as values. If you want only to consider empty cells, useCOUNTAas it is used in the AI code.
Sub DeleteBlankRows()
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("RawIPs")
Dim lo As ListObject, rg As Range, urg As Range, rrg As Range
Dim ColumnsCount As Long, IsFirstFound As Boolean
For Each lo In ws.ListObjects
If lo.ListRows.Count > 0 Then ' table is not empty
Set rg = lo.DataBodyRange
ColumnsCount = rg.Columns.Count
IsFirstFound = False ' reset for each table
For Each rrg In rg.Rows
If Application.CountBlank(rrg) = ColumnsCount Then ' is blank
If IsFirstFound Then ' all after first
Set urg = Union(urg, rrg)
Else ' first
Set urg = rrg
IsFirstFound = True
End If
'Else ' is not blank
End If
Next rrg
If IsFirstFound Then ' row ranges found
urg.Delete xlShiftUp
'Else ' no row ranges found
End If
'Else ' table is empty
End If
Next lo
MsgBox "Blank rows deleted from all tables.", vbInformation
End Sub