Excel VBA macro to find and clear/delete any cell with currency format
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
I need this to work when adding Macro to Personal.XLSB since I have 165 files I need to remove currency from.
I've never used a Personal.XLSB, but can't you just copy the macro I posted to your Personal.XLSB workbook?
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
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
Here is what I got to work. Thanks.
Sub ClearCurrency()
Dim cel As Range
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
For Each cel In Worksheets("Sheet1").Range
If VarType(cel) = vbCurrency Then
cel.Clear
End If
Next
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
Sub ClearCurrency()
Dim cel As Range
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
For Each cel In Worksheets("Sheet1").Range
If VarType(cel) = vbCurrency Then
cel.Clear
End If
Next
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
Premium Content
You need an Expert Office subscription to comment.Start Free Trial