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

[justme101]

Hello,

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

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.

 

[mohadin]

Try

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]

Another approach:

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

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]

Another Solution:

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]

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]

OR :

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 :

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]

Another way

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]

Another way

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]

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]

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.