3
Sub Macro9()
  Range("Table57[Weld Done]").Select
  Selection.Replace What:="0/1/1900", Replacement:="", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=True, FormulaVersion:=xlReplaceFormula2
  Range("Table57[[#Headers],[Weld Done]]").Select
End Sub

I use Replace function to replace default date 0/1/1900 with "blank" & it worked. So I recorded the workflow using VBA macro recording function & it gives coding as above.

When I try to use the macro again using the same code, it won't return the "blank". The value remains as default date 0/1/1900 like nothing happened.

All of you can refer below image for reference.

Your assistance is highly appreciated. Thanks.

Replace default date 0/1/1900 with "blank"

Pᴇʜ
  • 53,845
  • 9
  • 46
  • 68
jafnihusnan
  • 31
  • 1
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Apr 19 '22 at 07:31

2 Answers2

0

Just change "0/1/1900" to 0 will do.

Chui Ching
  • 19
  • 4
  • `"0/1/1900"` **is** `0`. The value zero will show as `"0/1/1900"` if formatted as date. – Pᴇʜ Apr 19 '22 at 07:18
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 19 '22 at 14:45
0

The issue is that the number 0 formatted as date is 0/1/1900

I assume that they are the result of a calculation (you use a formula in those cells). You can change the .NumberFormat of your range to something like:

[=0]"";D/M/YYYY

and this will hide the zero dates and show the cells as blanks.

Pᴇʜ
  • 53,845
  • 9
  • 46
  • 68
  • This is not what the OP asked. He asked how to use VBA to replace "0/1/1900" with "". Your answer does not attempt to deal with this. – Jonathan Willcock Apr 19 '22 at 19:29
  • @JonathanWillcock Since the values obviously come from a formula because nobody would enter a date like `0/1/1900` and the formula autodeploys over the listobject (formatted as table) it does not make sense to remove those as they won't update anymore when data changes. • So obviously the OP run into an [X/Y Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) where he asked the wrong question based on a wrong assumption. That's why my answer has a different solution. – Pᴇʜ Apr 20 '22 at 06:16