We help IT Professionals succeed at work.

Excel VBA macro to find and clear/delete any cell with currency format

Aaron Roessler
on
Simple Macro that will find any cell with format of currency and clear data.

Screen-Shot-2020-03-09-at-11.09.48-A.jpgFalcon.xlsx
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Sub ClearCurrency()
Dim cel As Range

With ThisWorkbook.ActiveSheet
    For Each cel In .UsedRange.Cells
        If VarType(cel) = vbCurrency Then
            cel.Clear
        End If
    Next
End With

End Sub

Open in new window

Aaron RoesslerWeb Developer

Author

Commented:
I need this to work when adding Macro to Personal.XLSB since I have 165 files I need to remove currency from.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I've never used a Personal.XLSB, but can't you just copy the macro I posted to your Personal.XLSB workbook?
Aaron RoesslerWeb Developer

Author

Commented:
This video will help explain whats happening.  https://www.loom.com/share/e3f187346d924e6abb77d435cb4c3b75

I have 160+ excel files I need to remove currency from... So the macro needs to run from a master file and not pasted into each of the 160+ files.  the Personal.xlsb is a Master doc thats always there when I open Excel
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Hopefully this article will help you.
Web Developer
Commented:
Here is what I got to work.  Thanks.

Sub ClearCurrency()
Dim cel As Range
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
For Each cel In Worksheets("Sheet1").Range("D1:G400").Cells
        If VarType(cel) = vbCurrency Then
            cel.Clear
        End If
Next
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

Explore More ContentExplore courses, solutions, and other research materials related to this topic.