M
meh2030
Is there a way to find out the default number formats for Excel? For
example, if you open the "Format Cells" dialog box (Menu Bar: Format /
Cells, or Ctrl+1), click the "Number" page, and select "Date", the
"Type:" box populates with a number of options. I'm asking if there
is a way to programmatically discover what these defaults are.
The reason I want to know this is because I received an Excel file
this past week that had so many Custom formats that the file wouldn't
let me make formatting changes. I've never seen this error before,
but I've also never seen so many custom formats before. Ultimately, I
would like to delete the non-default formats, and maintain the default
custom formats without accidentially deleting them (if that's even
possible). (Hopefully this makes sense).
My thought process is below. I can do the comparision part, but I
don't know how to find out what the default formats are. Any help is
greatly appreciated. Thanks in advance.
Matt
Sub test_Format()
Dim n As Variant
'place default formats into an array, e.g. formatAry()
For Each n In Selection.Cells
' Debug.Print n.Address; ": "; n.NumberFormat
'compare formatAry(i) to n.NumberFormat
'If n.NumberForat <> formatAry(i) Then
'n.DeleteNumberFormat x
Next
End Sub
example, if you open the "Format Cells" dialog box (Menu Bar: Format /
Cells, or Ctrl+1), click the "Number" page, and select "Date", the
"Type:" box populates with a number of options. I'm asking if there
is a way to programmatically discover what these defaults are.
The reason I want to know this is because I received an Excel file
this past week that had so many Custom formats that the file wouldn't
let me make formatting changes. I've never seen this error before,
but I've also never seen so many custom formats before. Ultimately, I
would like to delete the non-default formats, and maintain the default
custom formats without accidentially deleting them (if that's even
possible). (Hopefully this makes sense).
My thought process is below. I can do the comparision part, but I
don't know how to find out what the default formats are. Any help is
greatly appreciated. Thanks in advance.
Matt
Sub test_Format()
Dim n As Variant
'place default formats into an array, e.g. formatAry()
For Each n In Selection.Cells
' Debug.Print n.Address; ": "; n.NumberFormat
'compare formatAry(i) to n.NumberFormat
'If n.NumberForat <> formatAry(i) Then
'n.DeleteNumberFormat x
Next
End Sub