check if empty cell

[phoeberunner]

Hi,

I have many input columns in several sheets. I would like to enforce user to key in data in all input columns before running a macro. Empty cell is not allowed.

Is there an efficient way to check if all cells in input columns are filled?
Is looping every cell to check emptiness the only way?

Thanks,
Sityee

 

[Giordano Bruno]

Why not put a formula in a cell to check that there are no blanks and use the result in this cell to determine whether or not to allow the macro.

 

[njimack]

You could create a named range that refers to the cells in question. Then have your first line of code either sum (if they're numerical) or count (if they're text) the range.

 

[phoeberunner]

You could create a named range that refers to the cells in question. Then have your first line of code either sum (if they're numerical) or count (if they're text) the range.

Can you please clarify? the input columns are for numerical data. The sum of a named range could be 0 to a large number. As there are many input columns, user may overlook an empty cell within range, I can't allow even an empty cell in the input range.

 

[phoeberunner]

Why not put a formula in a cell to check that there are no blanks and use the result in this cell to determine whether or not to allow the macro.

Hi could you please clarify? If I put a formula in a cell, what would be the result to return if it is empty? I still need to loop through to see if the "result" exist, right?

 

[njimack]

Can you please clarify? the input columns are for numerical data. The sum of a named range could be 0 to a large number. As there are many input columns, user may overlook an empty cell within range, I can't allow even an empty cell in the input range.

1. Select the cells in question
2. Create a named range (Enter the name in the Name box directly above column A)
3. Your code would start something like this:

Sub test()
Dim r As Range
Set r = Range("Range1")
If WorksheetFunction.Sum(r) = 0 Then
    MsgBox "You haven't populated all the required cells", vbCritical
    Exit Sub
End If
'rest of your code here
End Sub

 

[Giordano Bruno]

To check that there are no empty cells with a formula: suppose the range is from A6 to A13, the formula =1+ROW(A13)-ROW(A6)-COUNT(A6:A13) will return the number of empty cells. If none, the formula will return "0"

 

[phoeberunner]

To check that there are no empty cells with a formula: suppose the range is from A6 to A13, the formula =1+ROW(A13)-ROW(A6)-COUNT(A6:A13) will return the number of empty cells. If none, the formula will return "0"

Thanks Gio, it solved my problem!