count blanks in column with msg box

[VbaHell]

Hello all and good morning

I want to count the number of blank cells in a column range and have a message box provide the answer
this is waht i have tried so far, any help would be great please

Dim LastRow As Long
Dim iBlank, rng As Range
 
LastRow = Sheets("Data").Range("B2").End(xlDown).Row


    Set rng = Sheets("Data").Range("AA2:AA" & LastRow).Find("")


    With WorksheetFunction
        iBlank = .CountA(rng)
    End With
    MsgBox "Blank: " & iBlank

 

[Yongle]

Counts cells that are truly empty (ie do not contain a value or a formula)

Sub EmptyCells()
    Dim LastRow As Long, rng As Range
    With Sheets("Data")
        LastRow = .Range("B" & Rows.Count).End(xlUp).Row
        Set rng = .Range("AA2:AA" & LastRow).SpecialCells(xlCellTypeBlanks)
    End With
    MsgBox "Truly empty: " & rng.Count
End Sub

But if some cells contain empty strings returned by formulas etc, use this

Sub CountCellsWithNoValue()
    Dim LastRow As Long, rng As Range
    With Sheets("Data")
        LastRow = .Range("B" & Rows.Count).End(xlUp).Row
        Set rng = .Range("AA2:AA" & LastRow)
    End With
    MsgBox "No Value: " & WorksheetFunction.CountIf(rng, "")
End Sub

 

[VbaHell]

THis is great Yongle

Many thanks mate

 

[Yongle]

thanks for the feedback