Remove "-" from a excel file

[Lasantha]

Hi Team,
I am getting an attached file from our system, and it contains "-" for some empty cells. I want to remove those or replace them with "0" but this should not affect to the negative figures. It would be great if you could give me a VBA code to do this.

Thank you,
Lasantha.

 

[vletm]

Lasantha​

You wrote ... it contains "-" for some empty cells. I want to remove those or replace them with "0" ...
... that could be possible to solve like below

There are none "-" to replace.
Did You mean something like below?

If so - then do above settings and press [ Replace All ].
Or
Have You tried to Record Macro to get Your wanted code Yourself?

 

[AC PORTA VIA]

try this.

>>> use code - tags <<<

Sub Lasantha()
    Dim rng As Range, r As Range, rSel As Range
Dim lastRow As Long
    lastRow = Range("O" & Rows.Count).End(xlUp).row


    Set rng = Range("C2:O" & lastRow)
    Set rSel = Nothing

    For Each r In rng
        If r.value = "-" Then
            If rSel Is Nothing Then
                Set rSel = r
            Else
                Set rSel = Union(rSel, r)
            End If
        End If
    Next r
    If Not rSel Is Nothing Then rSel.Select
     Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

 

[Frank2021]

Hi,

You can do it using "Find & Replace" dialog as @vletm is saying but additionaly check "Find entire cells only" option, that's the key, and negative numbers won't be affect at all and no macro is needed for this case

Regards

 

[pecoflyer]

This can get you started, you will have to find out how to select your range

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim Rng As Range

    '~~> Change this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    '~~> Change this to the relevant range
    Set Rng = ws.Range("A2:A300")

    Rng.Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

 

[jolivanes]

Sub Maybe()
    Sheets("Sheet1").UsedRange.Replace "-", ""
End Sub