Question posted 2011 · +7 upvotes
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.
Accepted answer +12 upvotes
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).
Top vba Q&A (6)
- Difference between Visual Basic 6.0 and VBA +122 (2009)
- VBA – how to conditionally skip a for loop iteration +116 (2011)
- VBA: Test if string begins with a string? +53 (2013)
- html parsing of cricinfo scorecards +47 (2012)
- Code to loop through all records in MS Access +46 (2011)
- Access VBA | How to replace parts of a string with another string +44 (2011)
vba solutions on this site
.