Selecting Special Cells

[batau65]

I've a macro to select in column D all constants and delete them. The macro runs fine whenever there are constants in this column, and is as follow:

Columns("D:D").Select
Selection.SpecialCells(xlCellTypeConstants, 1).Select
Selection.ClearContents

However when there are no constant, the macro ends with 'Run-time error 1004.' No cells were found.

How do I modify this macro to stop (or skip to the next macro line) if it does not find any constant, and if it does, delete them (as currently done)?

 

[Zathras]

A simple way is to use error trapping (See the help file):

  Columns("D:D").Select
  On Error GoTo Cleared
  Selection.SpecialCells(xlCellTypeConstants, 1).Select
  Selection.ClearContents
Cleared:
  On Error GoTo 0

 

[PHV]

Perhaps this ?
Dim R As Range
On Error Resume Next
Set R = Columns("D").SpecialCells(xlCellTypeConstants, 1)
If Err.Number = 0 Then R.ClearContents
Err.Clear
On Error Resume Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[batau65]

Thank you, Zathras and PHV for the super fast reply. I tried both method and both worked. Again, thx.