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

Answer 3

try in this way:

Dim nonBlanck as long
    nonBlanck = WorksheetFunction.CountA(WB.Sheets(1). _
              Range(Cells(2, X), Cells(2, Y)). _   
                         SpecialCells(xlCellTypeConstants))

However, if WB.SHeets(1) is not active then you could have some problems. Therefore try with this improved solution:

Dim nonBlanck as long
    With  WB.Sheets(1)
        nonBlanck = WorksheetFunction.CountA( _
              .Range(.Cells(2, X), .Cells(2, Y)). _   
                         SpecialCells(xlCellTypeConstants))
    End With

Answer 4

You need a way to define the range of clipped columns and then apply SpecialCells:

Sub dural()
    Dim x As Long, y As Long, rng As Range

    x = 3
    y = 5
    Set rng = Range(Cells(2, x), Cells(Rows.Count, y))
    MsgBox rng.Cells.SpecialCells(xlCellTypeConstants).Count
End Sub