VBA clear character from all sheets only partly working

[albytross]

Hi all,

I have the following VBA code - I want to clear any commas from the entire workbook, and all worksheets.
I plan to export all worksheets as CSV's, so errant commas can cause problems.

The below code seems to only clear commas from some sheets and I can't fix it.
I'm using xlPart to remove commas from within cells

Sub RemoveCommas()

Dim ws As Worksheet
Dim Search As String
Dim Replacement As String
Dim Prompt As String
Dim Title As String
Dim MatchCase As Boolean

For Each ws In Worksheets
ws.Cells.Replace what:=",", Replacement:="", _
LookAt:=xlPart, MatchCase:=False
Next

End Sub

 

[footoo]

Do the cells where it's not working contain numbers?

 

[albytross]

It seems to be a mix. There are some sheets with only commas in the cell, and they also dont work. Id like all commas in all cell types to be removed

 

[footoo]

See if this is any better:

Sub RemoveCommas()

Dim ws As Worksheet

For Each ws In Worksheets
    With ws.UsedRange
        .Replace what:=",", Replacement:="", _
            LookAt:=xlPart, MatchCase:=False
        .NumberFormat = "General"
    End With
Next

End Sub

 

[montecarlo2012]

Your VBA code looks good and should work to remove commas from all worksheets in the workbook. However, if you're finding that it's not working for some sheets, it's possible that those sheets have protected cells or are otherwise restricted in some way.

To work around this, you could add some error handling to the code to skip any sheets that it can't modify. Here's an updated version of your code that includes error handling:

Sub RemoveCommas()
Dim ws As Worksheet
Dim Search As String
Dim Replacement As String
Dim Prompt As String
Dim Title As String
Dim MatchCase As Boolean

On Error Resume Next ' continue to the next sheet if an error occurs

For Each ws In Worksheets
    ws.Unprotect ' remove any sheet protection
    ws.Cells.Replace what:=",", Replacement:="", LookAt:=xlPart, MatchCase:=False
    ws.Protect ' restore sheet protection
Next

On Error GoTo 0 ' turn off error handling

End Sub

This updated code includes the On Error Resume Next statement at the beginning of the loop to continue to the next sheet if an error occurs. It also includes the On Error GoTo 0 statement at the end to turn off error handling.

Additionally, it removes any sheet protection before modifying the cells and then restores the protection afterwards. This should ensure that all sheets are modified as intended, regardless of any restrictions that may be in place.

 

[albytross]

Thanks both, great additions and suggestions thanks