VBA checking if certain cells are empty

[willow1985]

Hello all,

I was hoping someone would be able to help me in modifying a VBA code

The below code checks if a certain range is blank or not and proceeds accordingly. The range is determined from where the cell is selected (which is why relative references is used) and if Columns B to K are blank in that particular row the macro will not proceed.

What I would like to do is add one more column which is Column S (aka Cells(Selection.Row 19)

How would I go about doing this?

Thank you to any who can help!

  Dim msg As String, ans As Variant, lr As Long, sh As Worksheet, lo As ListObject
 
  If Application.CountIf(Sheets("NCR Log").Range(Cells(Selection.Row, 1), Cells(Selection.Row, 11)), "") > 0 Then 'this line is what I require modified
  MsgBox "Please Complete all manditory fields (Columns B to K & S)"
Range(Cells(Selection.Row, 17), Cells(Selection.Row, 17)).ClearContents
    Exit Sub
  End If
msg = "Your NCR File will be finalized," & vbCrLf & "" & vbCrLf & " Do you wish to continue?"

    ans = MsgBox(msg, vbYesNo)

    Select Case ans

        Case vbYes

 

[Joe4]

What I would like to do is add one more column which is Column S (aka Cells(Selection.Row 19)

Change your COUNTIF to COUNTIFS instead, which allows you to check multiple conditions, and follow the same format you are now currently using to check column 11.

See: MS Excel: How to use the COUNTIFS Function (WS)

 

[Alex Blakenburg]

The simplest would be somerhing like the below.
Your description tends to encourage an interpretation of All fields being blank when in fact per the code you want if "ANY" are blank.
PS: I don't think Countifs will help you since you want an OR condition not an AND condition.

If Application.CountIf(Sheets("NCR Log").Range(Cells(Selection.Row, 1), Cells(Selection.Row, 11)), "") > 0 _
        Or Sheets("NCR Log").Cells(Selection.Row, "S") = "" Then
  MsgBox "Please Complete all manditory fields (Columns B to K & S)"

 

[Siddharth Rout]

Is this what you are trying? I have commented the code. Let me know if you still have any questions...

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    Dim rw As Long
    Dim totalCells As Long
    
    '~~> Set this to the relevant worksheet
    Set ws = Sheet1
    
    '~~> Check if the selection is a valid range
    If TypeName(Selection) <> "Range" Then
        MsgBox "Select a range first."
        Exit Sub
    End If
    
    '~~> Get the row of the selection
    rw = Selection.Row
    
    '~~> Construct your range
    Set rng = ws.Range("A" & rw & ":K" & rw & ",S" & rw)
    'MsgBox rng.Address
    
    '~~> This is the total count of the cells in the above range
    totalCells = rng.Cells.Count
    
    '~~> Here we are checking if the [filled cells = total count of the cells]
    '~~> If it is not same that means there is at least 1 blank cell.
    If Application.WorksheetFunction.CountA(rng) <> totalCells Then
        MsgBox "Please Complete all manditory fields (Columns A to K & S)"
        Exit Sub
    End If
    
    '~~> Rest of the code
End Sub

 

[willow1985]

The simplest would be somerhing like the below.
Your description tends to encourage an interpretation of All fields being blank when in fact per the code you want if "ANY" are blank.
PS: I don't think Countifs will help you since you want an OR condition not an AND condition.

If Application.CountIf(Sheets("NCR Log").Range(Cells(Selection.Row, 1), Cells(Selection.Row, 11)), "") > 0 _
        Or Sheets("NCR Log").Cells(Selection.Row, "S") = "" Then
  MsgBox "Please Complete all manditory fields (Columns B to K & S)"

This works perfect! Thank you very much

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.