Trying To Delete Empty Rows From Table - Invalid Procedure Error

[JonnieO]

Hell All:

I have a worksheet that allows a user to input data. One of the options allows them to click a button and have additional rows added to the table. This part is working fine.

However, when they click the button to export that data as a CSV file, it must remove any empty rows, so the DB will not error by seeing rows of blank data. You see the CSV file is imported into a DB.

For some reason when the "Delete Blanks" routine is called it errors out with an error: "Run-time error '5': Invalid procedure call or argument" The code follows:

Sub Delete_Blank_Rows()
Dim RowCount As Long
Dim LastRow As Integer
Dim tbl As ListObject


RowCount = Sheets("Billing_Data").ListObjects("Table35").Range.Rows.Count
LastRow = RowCount
Set tbl = Range("tblBilling_Data").ListObject
ActiveSheet.Unprotect Password:="password"
tbl.Range("E5:E200").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.Protect Password:="password", AllowFiltering:=True, Contents:=True, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=True
End Sub

Any help would be greatly appreciated.

 

[JonnieO]

Hell All:

I have a worksheet that allows a user to input data. One of the options allows them to click a button and have additional rows added to the table. This part is working fine.

However, when they click the button to export that data as a CSV file, it must remove any empty rows, so the DB will not error by seeing rows of blank data. You see the CSV file is imported into a DB.

For some reason when the "Delete Blanks" routine is called it errors out with an error: "Run-time error '5': Invalid procedure call or argument" The code follows:

Sub Delete_Blank_Rows()
Dim RowCount As Long
Dim LastRow As Integer
Dim tbl As ListObject


RowCount = Sheets("Billing_Data").ListObjects("Table35").Range.Rows.Count
LastRow = RowCount
Set tbl = Range("tblBilling_Data").ListObject
ActiveSheet.Unprotect Password:="password"
tbl.Range("E5:E200").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.Protect Password:="password", AllowFiltering:=True, Contents:=True, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=True
End Sub

Any help would be greatly appreciated.

FYI, the error is occurring here:

tbl.Range("E5:E200").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

 

[D3allamerican07]

JonnieO,

This script worked for me. Be careful with the Table##.

Sub Delete_Blank_Rows()

Dim RowCount As Long
Dim LastRow As Integer
Dim tbl As ListObject


RowCount = Sheets("Billing_Data").ListObjects("Table35").Range.Rows.Count

ActiveSheet.Unprotect Password:="password"

'Store blank cells inside a variable
  On Error GoTo NoBlanksFound
    Set Rng = Range("E5:E" & RowCount).SpecialCells(xlCellTypeBlanks)
  On Error GoTo 0

'Delete blank cells and shift upward
  Rng.Rows.Delete Shift:=xlShiftUp

'ERROR HANLDER
NoBlanksFound:
ActiveSheet.Protect Password:="password", AllowFiltering:=True, Contents:=True, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=True

End Sub

Let me know if you have any issues with this.

Bill

 

[Joe4]

Also, if you getting the error, are you sure that there are blank rows in that table?

 

[JonnieO]

Also, if you getting the error, are you sure that there are blank rows in that table?

I think both your Joe4 and D3allamerican07 are onto something. I was having issues with formulas not being "passed" to a new row or rows, so I inserted some code that adds the formula via VBA, which is working fine. BUT, it appears that the formulas in the cells cause the cell not to be "blank". Also, as noted by D3allamerican07, I did not have an error handler at all, which is bad practice.

This leads me to this question, can I tell the code to delete the data row if there is no VALUE in a cell, even if there is a formula?

 

[Kuljack]

You could take this approach, it will start at row 2 to ignore heads but can be adjusted as needed. If a formula was present, but was providing a blank value in the cell it would be deleted.

Sub DeleteBlanks()
Application.ScreenUpdating = False
Dim Y As Integer
Dim CellValue As String
WSLastRow = ActiveSheet.UsedRange.Rows.Count 'Finds the last row in your worksheet (WS)
Y = 5 'Row to review for blanks
For Each ValX In Range(Cells(2, Y), Cells(WSLastRow, Y))
    CellValue = Cells(ValX.Row, Y)
    If CellValue = "" Then
    Rows(ValX.Row).Delete
    End If
Next
Application.ScreenUpdating = True
End Sub