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

--
Gary''s Student - gsnu201001


"MrAcquire" wrote:

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?

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?


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu201001


"MrAcquire" wrote:

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?

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?

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu201001


"MrAcquire" wrote:

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?