CODEWITHSUNDEEP
excelvbareplace
Gary Nolan
Gary Nolan

Reputation: 111

Replacing Defined Characters

Attempting to write a Sub to replace defined characters in a cell. I've defined all the characters in an array named SC.

I am looping through each populated cell in Column 1, and I want it to look at them one at a time, and if it finds anything in that cell that matches what's in my array SC, I want it to replace that character with a blank. I get a type 13 mismatch when I run this, on line "Cells(i, 1) = Replace(Cells(i, 1), SC, "")".

Sub SpecialCharactersRemoval()
Dim SC As Variant
Dim lr As Long

lr = Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Rows.Count

For i = 1 To lr
SC = Array("~", "!", "@", "#", "$", "%", "^", "&", "*", "(", ")", ":", ";", "<", ">", "?", "|", "{", "}", "[", "]", ",", "+", "_")
Cells(i, 1) = Replace(Cells(i, 1), SC, "")
Next i
End Sub

Any thoughts on how I can get this code to do what I want? Thank you.

Upvotes: 0

Views: 45

Answers (1)

Warcupine
Warcupine

Reputation: 4640

After you define SC loop through it so replace is using each element instead of the entire array.

Sub SpecialCharactersRemoval()
Dim SC As Variant
Dim lr As Long
Dim ele As Variant
Dim i As Long

With ActiveSheet 'You can change this to whatever sheet you want to do this on
    lr = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Rows.Count 
    
    For i = 1 To lr
        SC = Array("~", "!", "@", "#", "$", "%", "^", "&", "*", "(", ")", ":", ";", "<", ">", "?", "|", "{", "}", "[", "]", ",", "+", "_")
        For Each ele In SC
            .Cells(i, 1) = Replace(.Cells(i, 1), ele, "")
        Next ele
    Next i
End With
End Sub

You may want to change lr = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Rows.Count to lr = .Cells(.Rows.Count, 1).End(xlUp).Row It will give more consistent results.

If the cells with @ are hyperlinks of any kind removing the @ will not remove the hyperlink.

If you don't want to loop through the cells as well (As per Scott Craner's comment), you will need to tweak a couple things. "*" will need to be "~*" same goes for "?" to "~?" as they are wildcards and will delete other stuff in this situation.

Sub SpecialCharactersRemoval()
Dim SC As Variant
Dim lr As Long
Dim i As Long

With ActiveSheet
    SC = Array("~", "!", "@", "#", "$", "%", "^", "&", "~*", "(", ")", ":", ";", "<", ">", "~?", "|", "{", "}", "[", "]", ",", "+", "_")
    For i = 0 To UBound(SC)
        .Range("A:A").Replace SC(i), ""
    Next i
End With
End Sub

Upvotes: 1

Related Questions