VBA - Count Non Blank Cells Using Cell Reference

Question

In VBA, using Excel 2016, I am trying to count the number of non-blank cells for a given range but using only the cell integer reference. I've tried the following:

WB.Sheets(1).Range(Cells(2, X), _
Cells(2, Y)).Cells.SpecialCells(xlCellTypeConstants).count

Where X and Y are cell references for the columns. Please assist.

Answer 1

You can do this using the excel formula CountA as you're probably aware.

You can also use this in VBA using:

with WB.sheets(1)
    WorksheetFunction.CountA(Range(.Cells(2, X), .Cells(2, Y)))
end with

Answer 2

Try this:

Range(WB.Sheets(1).Cells(2, X), _
WB.Sheets(1).Cells(2, Y)).Cells.SpecialCells(xlCellTypeConstants).count

You should add the workbook and worksheet before the cell and not before the range