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

Hi All,

I use the above code to delete the entire row of a cell in columnA if the cell is blank. This works fine with tables having blank cells in ColumnA but when VBA is not able to find any blank rows it can delete it shoots me an error. Is it really setup that way? I don't want to add an On Error as I don't want the rest of the lines of codes be affected by any on-error-then-skip codes.

Sarah

user3682866
  • 89
  • 2
  • 4
  • 8
  • `I don't want to add an On Error...` - [I know what you mean](http://stackoverflow.com/questions/21673529/if-not-function-proceeds-when-value-is-the-one-specified/21673617#21673617). However you can add `On Error Resume Next` before `Sheet1.Columns("A:A")...` and `On Error GoTo 0` after it (`On Error GoTo 0` will return your error handler to default mode.) – Dmitry Pavliv Jun 01 '14 at 06:59

1 Answers1

1

Is it really setup that way?

Yes, SpecialCells(xlCellTypeBlanks) throws runtime error if there is no blank cells.

As I mentioned in comments, it's general practice to "sandwich" code with OERN statemtent:

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

I don't want to add an On Error as I don't want the rest of the lines of codes be affected by any on-error-then-skip codes.

You're absolutelly right - using On Error Resume Next without On Error GoTo 0 really bad idea. That's why I'm using line On Error GoTo 0 which returns your error handler to default mode.

Community
  • 1
  • 1
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80