Check through populated cells to Cancel Save

[vvsp1995]

Hello I've created the below Macro in order to check that if column A is populated then column C must also be populated. However, I am only able to do that for a single row.

How can I enhance this macro so that excel will check all the instances where a row in Column A is populated and check against Column C to ensure that is also a populated cell? I assume I need a for loop, but haven't come across a thread that targets my specific issue. Thanks in Advance!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Range("A2").Value <> "" And Range("C2") = "" Then
Cancel = True
MsgBox "Save cancelled"
End If
End Sub

 

[Joe4]

Welcome to the Board!

I am assuming that your workbook only has one sheet, as you do not have any sheet references in your code (so it will automatically just run against the active sheet).
Try this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim lr As Long
    Dim r As Long
    
'   Find last entry in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows
    For r = 2 To lr
'       Check values of columns A and C
        If Cells(r, "A").Value <> "" And Cells(r, "C") = "" Then
            Cancel = True
            MsgBox "Save cancelled"
            Exit For
        End If
    Next r
    
End Sub