CODEWITHSUNDEEP
vbaexcel
Marco
Marco

Reputation: 95

IF Null exit sub code

I'm trying to check that if cells are empty or null, that it would display a message and exit sub. Here's my code: 

With Worksheets(1).[D3:D4, D6:D14]
    If WorksheetFunction.CountA(.Cells) = 0 Then
        MsgBox "Nothing to generate" & vbNewLine & _
        "Set parameters and click generate."
        Exit Sub
    End If
End With

But the code only works if the entire [D3:D4, D6:D14] are empty. I want it to exit sub even just one of the cells are empty. All cells needs have content for it to continue sub. Please help.

Upvotes: 1

Views: 1974

Answers (2)

brettdj
brettdj

Reputation: 55702

You can also use SpecialCells:

Sub Check()
Dim rng1 As Range
On Error Resume Next

With Worksheets(1).[D3:D4, D6:D14]
Set rng1 = .SpecialCells(xlBlanks)
    If Not rng1 Is Nothing Then
        MsgBox "Nothing to generate" & vbNewLine & _
        "Set parameters and click generate."
        Exit Sub
    End If
End With

End Sub

Follow-up question

Sub Check2()
Dim rng1 As Range
Dim rng2 As Range

With Worksheets(1)
Set rng1 = .Range(.[D3:D4], .[D6:D14])
End With

On Error Resume Next
Set rng2 = rng1.SpecialCells(xlBlanks)
On Error GoTo 0

If Not rng1 Is Nothing Then
    MsgBox rng1.Cells.Count - rng2.Cells.Count & " were used"
Else
    MsgBox "all cells used"
End If

End Sub

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33692

You need to seach inside the Range, try the code below:

Sub CheckEmptyCellsinRange()

Dim Rng     As Range
Dim cell    As Range

Set Rng = Worksheets(1).[D3:D4, D6:D14]

For Each cell In Rng
    If IsEmpty(cell) Or IsNull(cell) Then
        MsgBox "Nothing to generate" & vbNewLine & _
        "Set parameters and click generate."
        Exit Sub        
    End If

Next cell

End Sub

Upvotes: 2

Related Questions