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.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Try
VBA Code:
Sub tests()
    Set rg = Union(Cells(1, 1).CurrentRegion.Resize(, 3), Cells(1, 1).CurrentRegion.Offset(, 4).Resize(, 1))
    For Each r In rg
        If r = "" Then
            MsgBox "Warning" & vbNewLine & r.Address & vbNewLine & " Not Filled"
        End If
    Next
End Sub
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
Another approach:

Assuming your form is form Column A to Column F try the following

VBA Code:
Sub justme101()
lr = Sheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr
    For j = 1 To 6
        If Cells(i, j) = "" And j <> 4 And j <> 6 Then Cells(i, j).Interior.Color = vbRed
    Next j
Next i
End Sub

this will fill your blank cells under blue headers red if they are empty, till last used row.

hth...
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,585
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Another Solution:
VBA Code:
Sub FilledForms()
Dim i As Long, Clr As Long, j As Long, Lr As Long, Lc As Long, EmCells As String, TFC As String
Clr = Cells(1, 1).Interior.Color
Debug.Print Clr
Lr = Range("A" & Rows.Count).End(xlUp).Row
Lc = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To Lr
For j = 2 To Lc
If Cells(1, j).Interior.Color = Clr And Cells(i, 1) <> "" Then
If Cells(i, j).Value = "" Then
EmCells = Cells(i, j).Address
If TFC = "" Then
TFC = EmCells
Else
TFC = TFC & ", " & EmCells
End If
End If
Else
End If
Next j
Next i
If TFC = "" Then
MsgBox "All Cells filled"
Else
MsgBox "Cells of " & TFC & " Should be filled"
End If
End Sub
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,206
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

VBA Code:
Sub v()
Dim r&: r = [A2:C14,E2:E14].Find("*", After:=[A2], SearchDirection:=xlPrevious).Row
With Union(Range("A2:C" & r), Range("E2:E" & r))
    If Application.CountA(.Cells) <> .Cells.Count Then
        MsgBox "There blank cells."
    Else: MsgBox "No blank cells."
    End If
End With
End Sub
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,206
Office Version
  1. 2016
Platform
  1. Windows
OR :

VBA Code:
Sub v()
Dim r&: r = [A2:C14,E2:E14].Find("*", After:=[A2], SearchDirection:=xlPrevious).Row
With Union(Range("A2:C" & r), Range("E2:E" & r))
    If Application.CountA(.Cells) <> .Cells.Count Then
        .SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed
    Else: MsgBox "No blank cells."
    End If
End With
End Sub

OR :

VBA Code:
Sub v()
Dim r&: r = [A2:C14,E2:E14].Find("*", After:=[A2], SearchDirection:=xlPrevious).Row
With Union(Range("A2:C" & r), Range("E2:E" & r))
    If Application.CountA(.Cells) <> .Cells.Count Then
        MsgBox .SpecialCells(xlCellTypeBlanks).Address(0, 0) & " should be filled."
    Else: MsgBox "No blank cells."
    End If
End With
End Sub
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,419
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Another way
VBA Code:
Sub Find_Blanks()
Dim rng As Range
On Error Resume Next
    Set rng = Intersect(Range("A1").CurrentRegion, 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
 

justme101

New Member
Joined
Nov 18, 2017
Messages
34
Another way
VBA Code:
Sub Find_Blanks()
Dim rng As Range
On Error Resume Next
    Set rng = Intersect(Range("A1").CurrentRegion, 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


Wow! so many responses. I am thrilled, but based on my understanding skill (novice), I tested this one, and it gave me the desired result. I just added "Exit Sub" after the MsgBox which tells there are blank cells, because that is what I need. But, this code shows the unused cells as blank too.

To explain using my example, I have data in ranges A1:F5; now, executing this code shows the blank cells in the message box, if any, in this range, which is what I need, but, it also shows that cells "A6:C15, E6:E15" are blank too. Can you figure out why?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,419
Office Version
  1. 365
Platform
  1. Windows
Are there formulas in any of the blank rows? Most (if not all) of the answers provided assume that those cells are empty.

Data / formulas in the same rows in another column to the right of column F would also cause the range to expand if there is not at least one completely empty column to act as a separator.
 

justme101

New Member
Joined
Nov 18, 2017
Messages
34
Are there formulas in any of the blank rows? Most (if not all) of the answers provided assume that those cells are empty.

Data / formulas in the same rows in another column to the right of column F would also cause the range to expand if there is not at least one completely empty column to act as a separator.
My apologies. I should have mentioned that those blank rows contain formulas or drop-down lists/data validation.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,204
Messages
5,623,357
Members
415,969
Latest member
Rey99

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