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

 

[max_max]

I attached example As set now it should not print page 2/4/6
Click buttom print_1 in sheet "tagli"
if you add other numbers in column M on the "misure" sheet, then it also prints page 2/4/6 and that's fine
The formula
If WorksheetFunction.CountA (Range ("AD3: BA121")) = 0 Then
it does not work with formula cells.

File Dropper - Online file sharing

Online file sharing

www.filedropper.com

 

[jasonb75]

The formula
If WorksheetFunction.CountA (Range ("AD3: BA121")) = 0 Then
it does not work with formula cells.

This is correct, it will count the blanks as well because they are not empty. This is why I provided you with a different method that should work.

 

[max_max]

Not work

If Evaluate("=COUNTIF(AD3:BA121,"""")") = 2856 Then '<<< 2856 = range AD3:BA121

 

[Alex Blakenburg]

No wonder nothing works. Everyone is working on the assumption that you have either empty cells or "" cells.
Your workbook is returning inconsistent values.
eg
AD3 > =IFERROR(COUNTIFS(INDEX(misure!$K$4:$DZ$1000,,ROWS(AD$3:AD4)),AE4),0)
RETURN VALUE = 0
AE3 > = =IFERROR(LARGE(INDEX(misure!$K$4:$DZ$1000,,ROWS(AB$3:AC3)),1+SUMPRODUCT($D3:AB3,MOD(COLUMN($D3:AB3)+1,2)))," ")
RETURN VALUE = " " (a space)

You might want to start with making the formulas consistent.

 

[max_max]

Updated formula throughout the range

=IFERROR(LARGE(INDEX(misure!$K$4:$DZ$1000,,ROWS(AB$3:AC3)),1+SUMPRODUCT($D3:AB3,MOD(COLUMN($D3:AB3)+1,2))),0)

formula not work

If WorksheetFunction.CountA(Range("AD3:BA121")) = 0 Then

 

[jasonb75]

formula not work

If WorksheetFunction.CountA(Range("AD3:BA121")) = 0 Then

I refer to my previous reply

it will count the blanks as well because they are not empty.

and the solution that I provided, which only failed because of the inconsistency in your other formulas.

edit:- or at least it would have worked if you had been consistent with using blanks as per your original question instead of changing them all to 0.

 

[Alex Blakenburg]

Now that you have settled on default value of 0 this would be an option.

If Application.CountIfs(Range("AD3:BA121"), "<>0") = 0 Then

 

[max_max]

Yes alex now it works.
Thank you max

 

[Alex Blakenburg]

Thanks for letting us know. Glad we were able to help.