Checking if al cells are blank in a fixed range

[justme101]

Hello,

I am working on a file, which has some drop down lists and formulas in a range A1:D5. User has to fill up these ranges by using these drop down lists in some cells and the formulated cells will get some values automatically, and then saves the data form this range to a new sheet (that part of the code is done).

I want to check if ALL cells in the range A1:D5 are blank, meaning the user has not filled up anything, and show a msgbox saying "all cells are blank" and exits sub. I am not able to figure this out. I found this code on the net, but it doesn't work:


Dim count As Long
count = WorksheetFunction.CountA(Range("A1:D5"))

If count = 0 Then
MsgBox "All cells are empty, please fill data to be saved."
Else
End If

 

[maabadi]

CountA only counts texts Not Numbers. If you have numbers you can use CountBlank or CountIF function. Example:

Dim count As Long
count = WorksheetFunction.CountBlank(Range("A1:D5"))

If count = 20 Then
MsgBox "All cells are empty, please fill data to be saved."
Else
End If

 

[justme101]

CountA only counts texts Not Numbers. If you have numbers you can use CountBlank or CountIF function. Example:

Dim count As Long
count = WorksheetFunction.CountBlank(Range("A1:D5"))

If count = 20 Then
MsgBox "All cells are empty, please fill data to be saved."
Else
End If

Did the trick! thank you.

But, now there is another issue, I'll open a separate thread for it.

 

[maabadi]

You're Welcome & Thanks for feedback.