vba blank cells with formulas

[max_max]

Hello everyone
this function does not work if the range cells have formulas

If WorksheetFunction.CountA(Range("A3:A100")) = 0 Then

i tried with this macro

Sub vba_check_empty_cells()

Dim i As Long
Dim c As Long
Dim myRange As Range
Dim myCell As Range

Set myRange = Range("a3:a100")

For Each myCell In myRange
    c = c + 1
    If IsEmpty(myCell) Then
        i = i + 1
    End If
Next myCell

MsgBox _
"There are total " & i & " empty cell(s) out of " & c & "."

End Sub

the range is not empty

 

[jasonb75]

Try using

If Len(myCell.Value) = 0 Then

with the loop code or evaluate COUNTIF(A3:A100,"") to get a simple count of the blank cells.

 

[max_max]

hi jason, i didn't understand how to do it

 

[maabadi]

How about:

Sub vba_check_empty_cells()

Dim i As Long
Dim c As Long
Dim myRange As Range
Dim myCell As Range

Set myRange = Range("a3:a100")
c = myRange.Rows.Count
i = Application.WorksheetFunction.CountIf(myRange, "")

MsgBox _
"There are total " & i & " empty cell(s) out of " & c & "."

End Sub

Or this:

Sub vba_check_empty_cells()

MsgBox "There are total " & Application.WorksheetFunction.CountIf(Range("a3:a100"), "") & " empty cell(s) out of " & Range("a3:a100").Rows.Count & "."

End Sub

 

[max_max]

In the range
Range("a3:a100")
there are formulas

 

[maabadi]

In the range
Range("a3:a100")
there are formulas

What you want calculate exactly for i and c character at VBA Code?
if you want count empty cells and formulas that has empty result, this codes working correctly.

 

[maabadi]

if you want calculate number of cells with formula try this line:

c= Range("a3:a100").SpecialCells(xlCellTypeFormulas).Count

 

[jasonb75]

hi jason, i didn't understand how to do it

What are you actually trying to do? The whole task, not just the part that you have asked here.

There are multiple ways to count blank / empty cells, what you want to do with that count after may make one method preferable to another.

 

[max_max]

I badly explained myself.
I don't need to know how many empty cells there are in the range "a3:a100"
but if the range is empty

If WorksheetFunction.CountA(Range("A3:A100")) = 0 Then

the formula does not work if there are formulas in the range

 

[jasonb75]

You have a range of 98 cells, so

If Evaluate("=COUNTIF(A3:A100,"""")") =98 Then

Should result True if all are empty.

Does that help?