Check multiple cells and message box if data missing

Question

Is there a way to check multiple ranges, if they are blank then display a message box and tell me which ones have data missing? At the moment I'm typing this out and doing loads of different ones. Was wondering if there is an easier way?

My range is: Range("C11:D11,F11:G11,I11:J11,C14:F14,I14:J14,C15:F15,I15:J15,B18:J18,B42:J42"))

If WorksheetFunction.CountA(Range("C9:E9")) = 0 Then
 Worksheets("Create Form").Range("C9:E9").Select
 MsgBox "Please enter information in the required fields."
 Exit Sub
End If

If WorksheetFunction.CountA(Range("H9:J9")) = 0 Then
 Worksheets("Create Form").Range("H9:J9").Select
 MsgBox "Please enter information in the required fields."
 Exit Sub
End If

Thank you

Shai Rado Shai Rado · Accepted Answer · 2017-03-16T12:25:27

There is a way to check multiple Range, and in the code below it is implemented by using a For loop through all Areas of that Range.

Then for each CurRng (represnting an area), check if WorksheetFunction.CountA(CurRng) = 0.

Code

Option Explicit

Sub CheckMultipleRanges()

Dim MyMultiRng      As Range
Dim CurRng          As Range

With Worksheets("Create Form")   
   ' use a variable to set the multiple ranges in it 
   Set MyMultiRng = .Range("C11:D11,F11:G11,I11:J11,C14:F14,I14:J14,C15:F15,I15:J15,B18:J18,B42:J42")

   ' loop through all areas in the multi-range
    For Each CurRng In MyMultiRng.Areas
        If WorksheetFunction.CountA(CurRng) = 0 Then
            CurRng.Select
            MsgBox "Please enter information in the required fields."
            Exit Sub
        End If
    Next CurRng
End With

End Sub

Check multiple cells and message box if data missing

1 Answers