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

justme101

New Member
Joined
Nov 18, 2017
Messages
34
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,297
Office Version
  1. 2010
Platform
  1. Windows
try removing this line
VBA Code:
On Error GoTo 0
 

Watch MrExcel Video

Forum statistics

Threads
1,129,383
Messages
5,635,977
Members
416,890
Latest member
blader1989

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top