CODEWITHSUNDEEP
vbaexcelruntime-error
Kwon Black
Kwon Black

Reputation: 35

VBA Run Time Error 424: Object Required when trying to delete blank rows in a worksheet

From research, I found that the error is caused because an object cannot be recognized. I set the range to a specific column, thinking that the column("E") couldn't be identified possibly, but I still have the same error. How can I fix this error, or is there any better way to delete the rows in the worksheet? Thanks in advance.

Here's the program:

Sub DeleteBlanks()

Dim rng As Range
Dim wb As Workbook

Set wb = Workbooks.Open("IPIC-DATA-2.xlsx")

ws = wb.Sheets("Sheet1").Activate

Set rng = Range("E:E")

ws.Columns(rng).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

wb.Close SaveChanges:=True

End Sub

Upvotes: 1

Views: 836

Answers (1)

SJR
SJR

Reputation: 23081

Your syntax is a bit off. Define the worksheet and reference that. rng is already defined as a range (with reference to a sheet) so does not need to be wrapped in a sheet or column.

Sub DeleteBlanks()

Dim rng As Range
Dim wb As Workbook, ws As Worksheet

Set wb = Workbooks.Open("IPIC-DATA-2.xlsx")
Set ws = wb.Sheets("Sheet1")
Set rng = ws.Range("E:E")

On Error Resume Next 'avoid error if there are no blanks
rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

wb.Close SaveChanges:=True

End Sub

Upvotes: 3

Related Questions