How to check if a specific range of cells have any blank cell?

justme101

New Member
Joined
Nov 18, 2017
Messages
34
Hello,

I have prepared a simple form on an excel sheet for my team. Look at the sample below and the questions after that:

1614245020496.png


Assume this is the entire form. The number of rows are fixed (14), excluding header. Let's say the user has filled up 4 rows of data. What I need to do is to check whether all the cells in those 4 rows, where the column header is highlighted in BLUE, have been filled or not. No need to check the blank rows after the last data line. Can you help me with a VBA to do that? Please let me know if the explanation was not satisfactory. Thank you, in anticipation.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,449
Office Version
  1. 365
Platform
  1. Windows
I think that this should overcome any issues, although I've only done a very quick test.
VBA Code:
Sub Find_Blanks()
Dim rng As Range
On Error Resume Next
    Set rng = Intersect(Range("A1:F15").SpecialCells(xlConstants).EntireRow, Range("A:C,E:E")).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
    MsgBox "Blanks found" & vbCrLf & rng.Address(0, 0)
Else
    MsgBox "OK"
End If
End Sub
 
Solution

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

justme101

New Member
Joined
Nov 18, 2017
Messages
34
I think that this should overcome any issues, although I've only done a very quick test.
VBA Code:
Sub Find_Blanks()
Dim rng As Range
On Error Resume Next
    Set rng = Intersect(Range("A1:F15").SpecialCells(xlConstants).EntireRow, Range("A:C,E:E")).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
    MsgBox "Blanks found" & vbCrLf & rng.Address(0, 0)
Else
    MsgBox "OK"
End If
End Sub
YES! This works. Much appreciated. Thank you to everyone who took out time to respond. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,550
Messages
5,625,450
Members
416,107
Latest member
AVaes

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