Clear contents of cell

[Nanogirl21]

Is there a VBA code that I can use to clear contents of a cell if it ONLY contains a dash (-)? Some of the cells have a dash (-) with other characters & numbers. I want to keep those. I would like to run this for columns A-AP

EXAMPLE

CURRENT	WANTED RESULTS
BANANAS - APPLES - ORANGES	BANANAS - APPLES - ORANGES
-
CARROTS - CUCUMBERS	CARROTS - CUCUMBERS
-
12-09-2016	12-09-2019

<tbody>
</tbody>

 

[frabulator]

The simple answer would be

If Selection.Value = "-" Then
    Selection.Value = ""
End If

---Edit----
if you want to remove ever '-' in the workbook you could do this:

[COLOR=#333333][FONT=Verdana]Dim sht As Worksheet[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Dim x As Long[/FONT][/COLOR]





[COLOR=#333333][FONT=Verdana]'Loop through each worksheet in ActiveWorkbook[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]For Each sht In ActiveWorkbook.Worksheets[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]sht.Cells.Replace What:="-", Replacement:="", _[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]SearchFormat:=False, ReplaceFormat:=False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Next sht[/FONT][/COLOR]

 

[Nanogirl21]

The simple answer would be

If Selection.Value = "-" Then
    Selection.Value = ""
End If

Thank you.

Do I need to enter a range or anything else?

 

[frabulator]

Thank you.

Do I need to enter a range or anything else?

That code is based on the current selected cell, one cell. If you would like to have a selection of ranges then use the code below:

Sub del()


    Dim r As Long
    Dim c As Long
    Dim rc As Long
    Dim cc As Long
    
    r = Selection.Row
    c = Selection.Column
    rc = Selection.Rows.Count
    cc = Selection.Columns.Count
    
    For ro = r To rc + r - 1
        For co = c To cc + c - 1
            If ThisWorkbook.ActiveSheet.Cells(ro, co).Value = "-" Then
                ThisWorkbook.ActiveSheet.Cells(ro, co).Value = ""
            End If
        Next
    Next
    
    


End Sub

---EDIT---
The above code works by selection. Select a range and then run the macro. Inside the selection if a cells value is "-" then it will replace it with a "" (ie. an empty value).

 

[Nanogirl21]

thank you for the help.

 

[Nanogirl21]

That code is based on the current selected cell, one cell. If you would like to have a selection of ranges then use the code below:

Sub del()


    Dim r As Long
    Dim c As Long
    Dim rc As Long
    Dim cc As Long
    
    r = Selection.Row
    c = Selection.Column
    rc = Selection.Rows.Count
    cc = Selection.Columns.Count
    
    For ro = r To rc + r - 1
        For co = c To cc + c - 1
            If ThisWorkbook.ActiveSheet.Cells(ro, co).Value = "-" Then
                ThisWorkbook.ActiveSheet.Cells(ro, co).Value = ""
            End If
        Next
    Next
    
    


End Sub

---EDIT---
The above code works by selection. Select a range and then run the macro. Inside the selection if a cells value is "-" then it will replace it with a "" (ie. an empty value).

I am getting missmatch error when using both versions of the code.

 

[frabulator]

I am getting missmatch error when using both versions of the code.

Everything is working as expected on my end. Try to change the names of the variables (ie. change 'r' to 'SelR') and see if that fixes it. It might be that there are pre defined variables that share the same name as the ones used in that sub.

 

[Joe4]

Loops are totally unnecessary and inefficient in this case. The Find/Replace functionality has the option to match on the entire cell contents. This means it will only replace if the ENTIRE cell equals what you are looking for.

You could either use Find/Replace and do this all at once (no VBA required), or turn on the Macro Recorder to get the VBA code needed for it, i.e.

    Columns("A:AP").Replace What:="-", Replacement:="", [COLOR=#ff0000]LookAt:=xlWhole[/COLOR], _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[frabulator]

Loops are totally unnecessary and inefficient in this case. The Find/Replace functionality has the option to match on the entire cell contents. This means it will only replace if the ENTIRE cell equals what you are looking for.

You could either use Find/Replace and do this all at once (no VBA required), or turn on the Macro Recorder to get the VBA code needed for it, i.e.

    Columns("A:AP").Replace What:="-", Replacement:="", [COLOR=#ff0000]LookAt:=xlWhole[/COLOR], _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Joe is correct. That is a much better method then what I was doing

 

[Joe4]

A lot of people don't realize that the "Match entire cell contents" (which is the "LookAt" argument in VBA) exists.
You don't see it from the Find/Replace menu unless you click on the Options button.
It is a very useful little option!