Delete blank rows or skip to next if none

[cmschmitz24]

Hello, I need help expanding my VBA code to include if there are not blank rows then skip to next step of the macro code.

Here's what I have, but if there are no blank rows, then it bugs.

Range("A2:A15000").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Thanks!

 

[Joe4]

One simple way is to ignore the error, i.e.

On Error Resume Next
Range("A2:A15000").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

 

[cmschmitz24]

Works, thanks!

 

[Micron]

Pretty sure that go to 0 disables error handling in the currently running procedure. Might want to be aware of that as code could fail in unexpected ways. I'm on a cruise ship so I must leave it to you to verify.

 

[Joe4]

Pretty sure that go to 0 disables error handling in the currently running procedure. Might want to be aware of that as code could fail in unexpected ways. I'm on a cruise ship so I must leave it to you to verify.

Not quite. When you use a statement like "On Error Resume Next" (which ignores an error), using "On Error Goto 0" after it restores it back to the default setting.
By surrounding that one line of existing code with those two "On Error..." lines, it will only ignore errors from that one line of code.
See: VBA On Error Resume Next or Goto 0 - Automate Excel

 

[Micron]

While I'm not disagreeing with any particular point in that link (not fully read because I'm using a phone now) this is where I got the notion from. Quote:
On Error GoTo 0 disables error handling in the current procedure.
I think that to disagree suggests that the M$ info is wrong or perhaps not universally understood. Perhaps I will remember to do some testing when this vacation is over.

 

[Joe4]

I think we are actually probably talking about "apples and oranges" here ("error handling" vs. "default error messaging").
If they currently have a specific error handling routine in place, yes that would bypass it as is written.

But to invoke an error handling routine in the first place, you would need a line like:

On Error GoTo ErrorHandlingProcName

where ErrorHandlingProcName is the name of the procedure

So, if they have one already in place, they could simply just invoke that again like this:

On Error Resume Next
Range("A2:A15000").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo [I]ErrorHandlingProcName[/I]

Or, alternatively, they could add just update their current Error Handling Procedure to handle the error code produced by this situation.

If they do not currently have any error handling procedure in there code, they can use what I gave them "as-is". It will work.
Here is a simple example that shows that Excel default error messages return when using "On Error GoTo 0".

Sub MyTest()

    On Error Resume Next
    MsgBox "Test 1: " & 5 / 0
    On Error GoTo 0
   
    MsgBox "Test 2: " & 5 / 0
   
End Sub

You can see from the test, that the first test (the one between those two statements) will be ignored, while the second one will return the typical division by 0 error message.