VBA Code Help

Question

I am trying to write code to delete empty rows on a work sheet. it was setup buy recording a macro.

when I recorded it worked fine, when I ran the code I get run time error    "1004"

why wont it work as recorded?

Sub Macro11()

    Range("Table16[Tax Lot]").Select

    Selection.SpecialCells(xlCellTypeBlanks).Select

Selection.EntireRow.Delete

End Sub

Answer 1

Hi,

try this code to solve your problem...

data in active sheet

[edit..]

Sub Delete_ListRows_BlankCells_Column2()

Const sCol As Integer = 2 '<< search in column 2

Dim ws As Worksheet

Set ws = ActiveSheet

Dim obj As ListObject

Set obj = ws.ListObjects**("Table16")**

Dim N As Long, x As Long

Dim fg As Boolean

N = obj.ListRows.Count

For x = N To 1 Step -1

If Intersect(obj.ListColumns(sCol).Range, obj.ListRows(x).Range).Value = "" Then

fg = True

obj.ListRows(x).Delete

End If

Next x

If fg = False Then MsgBox "nothing found"

End Sub

or

using List column Name

Sub Delete_ListRows_BlankCells_ColumnName()

Const sName$ = "Tax Lot" '<< Column Name

Dim ws As Worksheet

Set ws = ActiveSheet

Dim obj As ListObject

Set obj = ws.ListObjects**("Table16")**

Dim N As Long, x As Long

Dim fg As Boolean

N = obj.ListRows.Count

For x = N To 1 Step -1

If Intersect(obj.ListColumns(sName).Range, obj.ListRows(x).Range).Value = "" Then

fg = True

obj.ListRows(x).Delete

End If

Next x

If fg = False Then MsgBox "nothing found"

End Sub

Note

you don't need to select data (blank cells) in column 2

Answer 2

Run time error 1004 is a generic error.  When you get the error and click Debug, which line is highlighted in yellow?

Most likely - The macro was already run and all the blank rows have been deleted, therefore there are no rows to select or delete, hence the error.

One way to handle this would be:

    Range("Table16[Tax Lot]").Select

    On Error Resume Next

    Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

This is not the best solution but it should work in your situation.  There are much more sophisticated ways to trap errors, but lets not get too fancy, but here is another:

    Range("Table16[Tax Lot]").Select

    On Error Resume Next

    Selection.SpecialCells(xlCellTypeBlanks).Select

    If Err = 0 Then

        Selection.EntireRow.Delete

    End If

Answer 3

so this is what I have, it selects the blanks but doesn't delete the rows. I wonder if it has something to do with the fact I am trying to apply this to a table?

The code did run without errors.

Sub Macro11()

 Range("Table16[Tax Lot]").Select

On Error Resume Next

Selection.SpecialCells(xlCellTypeBlanks).Select

If Err = 0 Then

Selection.EntireRow.Delete

End If

End Sub