How to clear cells in range that are of currency format without looping

user_9870211 0
Viewed 191

I am not sure this is a question for here, so shoot me if it's not!

I have a spreadsheet that contains data of both number and currency format. I am to delete the values in the cells that are of currency format only.

I can do this with a simple For Each loop, however, due to the size of the spreadsheet this is not efficient.

Option Explicit

Sub ClearCurrency()

    Dim myRange As Range
    Dim cell As Range

    Set myRange = Selection

    For Each cell In myRange
        If cell.NumberFormat = "$#,##0.00" Then
            cell.ClearContents
        End If
    Next cell

End Sub

I have read up on VarTypes and .NumberFormat but am unable to piece together a more efficient solution.

I know I cannot store the information to an array to loop through, so is there a faster way?

vba excel
2 Answers

As proposed in comments, you could use SearchFormat and ReplaceFormat to do this, something like the following:

Sub ClearCurrency()

If Not TypeOf Selection Is Excel.Range Then Exit Sub

With Application
    .FindFormat.Clear
    If .DecimalSeparator = "." Then .FindFormat.NumberFormat = "$#,##0.00"
    If .DecimalSeparator = "," Then .FindFormat.NumberFormat = "\$#,##0.00"
End With

Selection.Replace What:="*", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=False

End Sub

Noteworthy

Whilst working on non en_US local settings and making use of custom numberformat "$#,##0.00" you may encounter an Error 1004 warning. The format is not recognized and will error out. To prevent this you can either; make use of NumberFormatLocal:

Application.FindFormat.NumberFormatLocal = "$#.##0,00"

Notice the difference in both decimal comma and grouping character. A second option would be to escape the sequence with a backslash that indicates that the following character should be used literally as is:

Application.FindFormat.NumberFormat = "\$#,##0.00"

Now the decimal point and grouping character can stay in place. Therefore we need a check to test the local settings, which can be done with Application.DecimalSeparator, as proposed in the current solution.

user_92458530

This will do:

Set myRange = Selection

Application.FindFormat.NumberFormat = "$#,##0.00"
myRange.Replace What:="*", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=False
user_109332230
Related