Checking if cells are blank and showing the cell address in a msgbox

[justme101]

Hello,

I have a range of cells which are to be filled by the user. All of them either have a drop down or a formula in them. If a user starts to fill a row, he has to fill up the entire row and cannot leave anything blank. If all cells are left blank and he tries to save it, it will give a msgbox with a warning.

For the part, where all cells are left blank, the following code was written with the help of this forum, just a few seconds ago and it works fine:

'check if all cells are blank

Dim count As Long
count = WorksheetFunction.CountBlank(Range("C6:U19"))

If count = 266 Then 'if above range changes, this number will have to change as well
MsgBox "All cells are empty, please fill data to be saved."
Exit Sub
Else
End If

But, the part after that, where if the user has started entering data in a row, he cannot leave any cell blank in that row: the code I had was working fine until now, not sure what is wrong with it:

'check for empty cells for each entry

Dim rng As Range
On Error Resume Next
Set rng = Intersect(Range("C6:O15").SpecialCells(xlConstants).EntireRow, Range("C6:O15")).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
MsgBox "Blanks found" & vbCrLf & rng.Address(0, 0)
Exit Sub
Else
MsgBox "Data Saved Successfully. Please close the workbook. Thank you"
End If

Help please.

 

[diddi]

try removing this line

On Error GoTo 0

 

[justme101]

try removing this line

On Error GoTo 0

Nope. didn't work. It still saves the line even it is incomplete.