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.
Add a comment
|
5 Answers
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).
1 Comment
Enissay
Explaining what these extra lines do exactly would help a lot :-)
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.
3 Comments
brettdj
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
Doug Glancy
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.
brettdj
Cheers Doug. SpecialCells could be explained more intuitively that it is.
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.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
1 Comment
brettdj
+1 for the error handling suggestion. And I agree that "worksheet" is not the best variable name.
Another way:
If Range("Table2").Rows.Count > 1 Then
Range("Table2").EntireRow.Delete
End If
Comments
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
2 Comments
G42
Consider adding more to explain what the code is doing.