Fast question: get replace blanks to exit gracefully

[Imrhien]

G'day guys!

Should be an easy one for you pros. Here is my current script:

Columns("R:R").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "FALSE"

Goal: replace blanks in column R with the text "FALSE".
Issue: This works fine except when there are no blanks in the column - the script crashes and the line has to be removed to continue execution.

So, all I need is a method to get this script to exit gracefully when there are no blanks to convert. Can anyone help?

 

[nightcrawler23]

Sub temp()
On Error GoTo errHndl
Columns("R:R").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "FALSE"
errHndl:
End Sub

Use error handler.

 

[shg]

Or similarly,

Sub x()
    On Error Resume Next
    Columns("R").SpecialCells(xlCellTypeBlanks).Value = "FALSE"
End Sub

 

[Imrhien]

Thanks folks, that works flawlessly. Rather pleased with the first method; reminds me of batch scripting with the GOTO =D

This is very beneficial to me. I'm expecting non-savvy users to be using this script, so crushing any script crashes is vital. My macro is complete!

 

[mirabeau]

Thanks folks, that works flawlessly. Rather pleased with the first method; reminds me of batch scripting with the GOTO =D

This is very beneficial to me. I'm expecting non-savvy users to be using this script, so crushing any script crashes is vital. My macro is complete!

Actually, they're both essentially the same method.

Just that one has error handling terminating when the script terminates, the other it terminates at a specified point in the code.

And one selects the blanks/FALSE, the other doesn't. Selection is rarely needed in VBA code. Among other effects it can slow VBA codes considerably.

Also, if you've got a lot of blanks (>10k or so) then you're likely to have crashes using the special cells approach. Crashes from which the error handlers in the above codes will neither warn you nor save you.