If one cell in range is not empty, the all range must be not empty

[loroverde]

Hello
Im trying to run this code Ir: If one (or any) cell in the range A6:G6 and the cell O6 are not empty, then all range cells must be not empty.
If not, cancel save and show msg box.

This is the code iīm using inside a Sub SaveInvWithNewName()

Set wsf = WorksheetFunction
With Sheets("SERIGRAFIA")
  Set rngCheck = Union(.Range("A6:G6"), .Range("O6"))
  If wsf.CountBlank(.Range("A6:G6")) + wsf.CountBlank(.Range("O6")) = rngCheck.Cells.Count Then
  Cancel = False
  If wsf.CountBlank(.Range("A6:G6")) + wsf.CountBlank(.Range("O6")) < rngCheck.Cells.Count Then
    If wsf.CountA(.Range("A6:G6")) + wsf.CountA(.Range("O6")) < rngCheck.Cells.Count Then
      Cancel = True
      MsgBox "All cells on '" & .name & "' in '" & rngCheck.Address(0, 0) & "' must be filled!", vbExclamation, "Abort Saving"

---------------------------
The problem is than when I try to run this macro, if all the cells are blank, it "lock" the macro and it doesnīt run.
I want this code to run if all the cells are blank or all the cells are not blank, but cancel if one (or more) cell is blank and other arenīt.

Any solution?

Regards
Claudio.

[patel45]

attach please a sample file

[loroverde]

Here it goes the attachment.

[patel45]

Sub SaveInvWithNewName()
Set wsf = WorksheetFunction
Cancel = False
With Sheets("Hoja1")
  Set rngCheck = Union(.Range("A6:G6"), .Range("O6"))
  If wsf.CountBlank(.Range("A6:G6")) + wsf.CountBlank(.Range("O6")) < rngCheck.Cells.Count Then
    If wsf.CountA(.Range("A6:G6")) + wsf.CountA(.Range("O6")) < rngCheck.Cells.Count Then
      Cancel = True
      MsgBox "All cells on '" & .Name & "' in '" & rngCheck.Address(0, 0) & "' must be filled!", vbExclamation, "Abort Saving"
    End If
  End If
  If Cancel = False Then
      ActiveSheet.Copy
      NewFN = "C:\Users\Claudio Lari\Google Drive\claudio lari\Branders EIRL\Penprom\Cotizaciones\Cot_2013\" & ("#") & Range("C1") & ("--") & Range("P1").Value & Format(Date, "ddmmmyyyy") & ("-") & Range("B3") & ("-") & "penprom.xlsx"
      ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
      ActiveWorkbook.Close
  End If
End With

End Sub

[loroverde]

Thanks patel45, it works perfect, you are a genius.