Excel provides a function to check blank cells and also provide functions to check cells that are not empty.
CountA function will check cells that are not empty.
Countblank function as its name literally implies will check or count for blank or empty cells.
Example for Countblank function:
=COUNTBLANK(A1:B11) --- Function will check for empty spaces from A1 to B11 (2 columns)
=COUNTBLANK((A1:A11)) --- Function will check for empty spaces in a single column. Note the double parentheses
Example for CountA function:
=COUNTA(A1:A11) --- Function to check cells with values in a single column (Column A only)
=COUNTA(A1:B11) --- Functio to check cells with values in two columns from A1 to B11 (Column A and B)
To enter formula above using VBA:
Dim Cell_Formula As String
Dim xvalue As String
Cell_Formula = "=COUNTA(A1:A11)"
'Cell_Formula = "=COUNTBLANK((A1:A11))"
'Cell_Formula = "=COUNTBLANK(A1:B11)"
'Set cell formula using VBA
Worksheets("Sheet1").Range("F1").Formula = Cell_Formula
'To check for empty cells or not empty cells
xvalue = Trim((Worksheets("Sheet1").Range("B14").Value))
If xvalue = "" Then
MsgBox "Not Empty"
VBA code above will check for blank cells and also non-empty cells. It also shows how to enter the formula to a cell.
Cheers..till next time..