An empty cell equals zero and the empty string

Read the cell once and decide what it is in one place. After that the rest of the macro gets a typed value and never sees a Variant again.

Dim v As Variant
v = ws.Cells(r, 4).Value2

Select Case VarType(v)
    Case vbEmpty
        hasQty = False
    Case vbDouble
        hasQty = True
        qty = CDbl(v)
    Case vbString
        hasQty = False
        LogOddCell ws, r, v
    Case Else
        hasQty = False
        LogOddCell ws, r, v
End Select

Value2 matters here. With Value, a cell formatted as currency or a date arrives as vbCurrency or vbDate and drops into Case Else. An error cell shows up as vbError, which also lands in Case Else, so it gets logged rather than counted. LogOddCell stands for whatever you already do with skipped rows.

The person typing into that sheet has more ways to make "nothing" than the list has. They press Delete, and it's Empty. They tap the space bar to clear it, and it's a one-character string that looks blank on screen. They type '0 because the zero kept vanishing in some format, and now it's the text "0", which a vbDouble branch skips. All three look identical when you scroll past them. Is there a cheap way to show the user which cells the macro skipped, a fill colour or something, so the space-bar ones get fixed at the source?

Got curious whether Empty has a type of its own or just borrows one, and the Immediate window answer is that it borrows whatever the expression around it wants. Empty + 1 is 1, Empty & "x" is "x", and Empty = 0 and Empty = "" are both True. So it isn't really a value that equals zero. It's a value that turns into zero or into an empty string depending on who's asking, which is why the = 0 test looked like it worked for so long. The interesting question is which test notices the difference, and as far as I can tell only IsEmpty and VarType do.

I'd add one stage in front of that. Read the whole column into an array first, classify every cell, and only then compute, so the totals never start from a partly checked range. data = rng.Value2 gives a two-dimensional array with one element per cell, which is also much quicker than a Cells call per row.

There's a snag with small ranges, though. If the range is a single cell, Value2 returns the value itself, not an array, and data(1, 1) fails. So check IsArray(data) right after the read, and wrap the single value yourself before the classify step. Then the counts of empty, numeric and odd cells are known before anything is added up.

Count before fixing. On a sheet like that I'd want three numbers. Cells where IsEmpty is true, cells where it isn't but the value is "", and everything else. The middle group is the one people forget. A formula like =IF(B2="","",B2*C2) shows nothing, IsEmpty says False because the cell holds a formula, and VarType says vbString. Excel's COUNTBLANK counts those cells as blank, so a check against the worksheet count and a check in the macro can disagree, and the gap is that group.

Ha, Tcl has only one nothing, the empty string! The catch runs the other way. A missing value and legitimately empty text are the same string, so you check info exists or dict exists instead of the value.

Ran your three counts before touching anything, and that changed the story. Of the rows the macro flagged, only a handful were truly empty. The rest were formula blanks from a helper column somebody added (with a header that just says "x", thank you, whoever you were). So my IsEmpty fix would have caught almost none of them, and I'd have called it done.

Went with smallprint's Select Case on Value2, plus a Case vbString branch that treats "" as no quantity and anything else as a cell to log. The log sheet now has eleven entries, all of them the space-bar kind kim_dh described.