excel - How can I see when a cell had its contents cleared vs. set to 0 in a "Worksheet_Change" sub?

Question

I am writing a code in a "Worksheet_Change" sub where i need to store potential changes in values of cells (always integer). However, when a value is deleted, it appears as "0" in my worksheet, and I need it to show no value at all, since "0" and "nothing" is handled in completely different ways in my file.

So, how can I code so that I can tell the difference between what is being set as zero, and what is being deleted in order to, when I need to delete the value of a cell (so clear its contents), it will appear empty instead of with a zero?

I believe the problem can be somewhere in the following part:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim oldQty As Integer
Dim newQty As Integer

Application.EnableEvents = False
With Target
    newQty = .Value
    Application.Undo
    oldQty = .Value
    .Value = newQty
End With

Application.EnableEvents = True
    
(...)

End Sub

I need this to store the old quantity of the cell. I hope I was clear enough, any help is appreciated

Answer 1

Put the following line at the very beginning of Worksheet_Change.

If Target.Cells.Count > 1 Then Exit Sub 'prevent error in case of range edits

And change the following part

With Target
    newQty = .Value
    Application.Undo
    oldQty = .Value
    .Value = IIf(newQty = 0, vbNullString, newQty)
      'if value is zero return an epmty string instead of `0`
End With