Macro to clear contents of certain cells

  • Thread starter Thread starter MrAcquire
  • Start date Start date
M

MrAcquire

I have a range of cells that contains mostly numeric values but some cells
are populated with an alpha dash (-). I would like to clear the contents of
only those cells that contain a dash and leave the other cells untouched.

Replace won't work because it replaces a dash with an alpha blank cell and
the cell contents need to be clear or numeric to perform sumproduct()
calculations on them or else it results in a #VALUE! error. Yes, I know I
could replace "-" with 0, but I need the cell to be blank (no contents). I
assume a macro is required. Any ideas?
 
Sub ClearDash()
Dim s As String
s = "-"
For Each r In ActiveSheet.UsedRange
If InStr(r.Value, s) > 0 Then
r.Clear
End If
Next
End Sub
 
Thanks. Your macro achieves the desired result except that I had to modify
it to ClearContents instead of just Clear so it wouldn't clear the formatting
(see below)

Sub ClearDash()
Dim s As String
s = "-"
For Each r In ActiveSheet.UsedRange
If InStr(r.Value, s) > 0 Then
r.ClearContents
End If
Next
End Sub

But the macro results in a visual basic "Type mismatch" error when the macro
is finished. I just click OK and everything seems to have worked but macros
should not result in errors. Any idea what's causing this and how to fix it?
 
Thanks. I changed r.Clear to r.ClearContents and it worked as desired
(otherwise it clears the formatting, too). But the macro is resulting in a
visual basic Type Mismatch error when it's through. I click OK and the macro
appears to have worked but macros shouldn't result in error messages. Any
idea what's causing this error and how to fix it?
 
The problem is that by using "r.value" you are saying that the variable r is a number. Since you are trying to find "-" in the string and "-" is not a number yu will get the type mismatch error.

To resolve this declare r as a Range and then use "r.Text" to avoid the error.

Here is my code:

Sub ClearRefError()
Dim s As String
Dim r As Range
s = "REF"
For Each r In ActiveSheet.UsedRange
If InStr(r.Text, s) > 0 The
r.ClearContents
End If
Next
End Sub

Works like a champ in Excel 2010.
 
Back
Top