Deleting Empty rows in Excel using VBA

Question

I am trying to delete Empty rows by using below code:

worksheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

The code above is working fine, but giving run time error '1004': No Cells were found.

Answer 1

On Error Resume Next
worksheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

The error handling helps when there are no blank cells. SpecialCells(xlCellTypeBlanks) will always return an error if there are no cells like that so error handling is the only way (that I know of) to deal with it if you want to use SpecialCells(xlCellTypeBlanks).

Answer 2

You need to test that there are any blanks.

If WorksheetFunction.CountBlank(Worksheet.Columns("A:A")) > 0 Then
    Worksheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If

You can just use On Error Resume Next to skip over the line if there are no blanks, but it's generally preferable to test for a specific condition, rather than assuming you know what the error will be.

As far as I can see you'd only get the "No Cells Found" message if every cell in Column A has a value.

EDIT: Based on @brettdj's comments, here's an alternative that still uses CountBlank:

If WorksheetFunction.CountBlank(Intersect(worksheet.UsedRange, ws.Columns("A:A"))) > 0 Then
    worksheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If

Of course UsedRange is notoriously fickle and may be bigger than it appears. I think it's best to first determine the actual range where the rows are to be deleted and then check the SpecialCells in that range, e.g.:

Sub DeleteRows()
Dim ws As Excel.Worksheet
Dim LastRow As Long

Set ws = ActiveSheet
LastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
With ws.Range("A2:A" & LastRow)
    If WorksheetFunction.CountBlank(.Cells) > 0 Then
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End If
End With
End Sub

One last note - I changed the variable from "worksheet" to "ws" as "worksheet" is an Excel reserved word.

Answer 3

Working fine with me . These statement does not throw any error to me

 Sheet1.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete 'perfect

I found these type of solution for your question

 On Error Resume Next
 Sheet1.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 On Error GoTo 0

Take a look at these links

http://www.excelforum.com/excel-programming/390329-microsoft-visual-basic-run-time-error-1004-no-cells-were-found.html

http://www.mrexcel.com/forum/showthread.php?t=343744

and well yes did you set your object ? worksheet does not make any sense here

dim wsheet as worksheets
set wsheet = worksheets("worksheetname") or worksheets("sheet1")
wsheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete