We help IT Professionals succeed at work.

Excel Macro to delete specific characters

Member_2_5306354
Member_2_5306354 asked
on
I need an excel Macro to delete specific characters, namely " and ,
When it finds any quotes or commas it needs to delete these.
Comment
Watch Question

What is the extent of the data can you post a sample workbook ?
gowflow

Author

Commented:
Column A and Column B only, approximately 6000 lines.
try this code

Sub DelChar()
Dim WS As Worksheet
Dim cCell As Range
Dim sTemp As String

Set WS = ActiveSheet
For Each cCell In WS.UsedRange.SpecialCells(xlCellTypeConstants)
    sTemp = cCell.Value
    sTemp = Replace(sTemp, ",", "")
    sTemp = Replace(sTemp, """", "")
    cCell.Value = sTemp
Next cCell
End Sub

Open in new window


gowflow

Author

Commented:
Thanks. For some reason this is affecting the data in Column A in a manner that it is not desired.
What if I only wanted to apply these commands to Column B only??
ok then here it is:

Sub DelChar()
Dim WS As Worksheet
Dim cCell As Range
Dim sTemp As String

Set WS = ActiveSheet
For Each cCell In WS.Range("B:B").SpecialCells(xlCellTypeConstants)
    sTemp = cCell.Value
    sTemp = Replace(sTemp, ",", "")
    sTemp = Replace(sTemp, """", "")
    cCell.Value = sTemp
Next cCell
End Sub

Open in new window


gowflow