Add IF statement to Range

[jonathann3891]

Is it possible to add an IF statement to this code?

Private Sub CommandButton2_Click()
Dim LR As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
Range("C10:C" & LR).SpecialCells(xlCellTypeConstants, 23).Clear
End Sub

It works perfect, but if the cells are blank it give me the following error:
Run-time error '1004':
No cells were found

I dont want it go give an error if the cell is blank!

Thanks

 

[bbott]

You can add an error handling statement:

Private Sub CommandButton2_Click()
Dim LR As Long
On Error GoTo 500
LR = Range("C" & Rows.Count).End(xlUp).Row
Range("C10:C" & LR).SpecialCells(xlCellTypeConstants, 23).Clear
500
End Sub

 

[jonathann3891]

That works great!

But can you explain to me why that gets rid of the error?

 

[bbott]

This line:

On Error GoTo 500

basically says if the code errors, then go to the line designated as 500. In other words, it skips the code between the GoTo and 500 statements.

 

[jonathann3891]

I'm having a small problem. If I clear the data using this macro, and then it's accidently activated again it will clear everything on Column C.

I thought stating the range

Range("C10:C" & LR).

would stop the code from going beond the range I specified?

 

[bbott]

If your variable is set to the count of column C, it will clear everything from C10 down. Is that not what you want to happen?

 

[jonathann3891]

Thats what I want to happen. But if that range is empty and the macro is activated it will clear something that is on C5, etc.

 

[bbott]

I see. Try this:

Private Sub CommandButton2_Click()
If Range("C" & Rows.Count).End(xlUp).Row < 10 Then Exit Sub
Range("C10:C" & Range("C" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants, 23).Clear
End Sub

You don't need to use a variable or error handler with this.