10

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.

Luuklag
  • 3,897
  • 11
  • 38
  • 57
user569125
  • 1,423
  • 13
  • 29
  • 40

5 Answers5

15
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).

Chris Kent
  • 862
  • 11
  • 18
Jon49
  • 4,444
  • 4
  • 36
  • 73
8

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.

Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
  • Doug, that code will still fail for the OPs question as CountBlank as used above ill look at every cell in column A - so its a virtual certainty that this condition is always True. Whereas SpecialCells looks just at the usedrange, and there may be no blank cells, ie _"As far as I can see you'd only get the "No Cells Found" message if every cell in Column A has a value"_ is incorrect – brettdj Oct 25 '11 at 01:10
  • brettdj, thanks for the info. I wondered about the fact that the OP was using it on the full column, and now I understand how that might work. – Doug Glancy Oct 25 '11 at 04:17
  • Cheers Doug. SpecialCells could be explained more intuitively that it is. – brettdj Oct 25 '11 at 05:06
2

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
niko
  • 9,285
  • 27
  • 84
  • 131
  • +1 for the error handling suggestion. And I agree that "worksheet" is not the best variable name. – brettdj Oct 25 '11 at 01:01
1

Another way:

If Range("Table2").Rows.Count > 1 Then
   Range("Table2").EntireRow.Delete
End If
DanF
  • 11
  • 1
0
Sub delete_rows_blank()

t = 1
lastrow = ActiveSheet.UsedRange.Rows.Count
Do Until t = lastrow
If Cells(t, "A") = "" Then
Rows(t).Delete
End If
t = t + 1
Loop

End Sub
  • Consider adding more to explain what the code is doing. – G42 Feb 27 '18 at 21:37
  • sorry - the macro just loops through column "A" for empty cells and deletes row if so. (you can adjust lastrow or column letter to match your needs). Hope this helps ! :) –  Feb 28 '18 at 11:28