How to find next whole empty row?-CodePudding

How to edit my code that it first find "Alerts" in column and check if the entire row below "Alerts" is empty then add "checkbox" in A2 similarly in next iteration of for loop it makes a check box finding again the next whole empty row after "Alerts" and add "checkbox" at A7.

Dim y As Long

For y = LBound(box_name) To UBound(box_name)
    Empty_row_A_BOOL = Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    Debug.Print Empty_row_A_BOOL
    If Empty_row_A_BOOL = True Then
        Empty_row_A = Range("A" & Rows.Count).End(xlUp).Row   1
        Debug.Print Empty_row_A
        If IsEmpty(s1.Range("A" & CStr(Empty_row_A)).Value) = True And 
IsEmpty(s1.Range("B2").Value) = True Then
            s1.CHECKBOXES.Add(Left:=Range("A" & CStr(Empty_row_A)).Left, Top:=Range("A" & 
CStr(Empty_row_A)).Top, _
            Width:=Range("A" & CStr(Empty_row_A)).Width, Height:=Range("A" & 
CStr(Empty_row_A)).Height).Select
            With Selection
            .Caption = ""
            End With
            'Debug.Print box_name(y)
            s1.Range("A" & CStr(Empty_row_A)).Offset(, 1).Value = "set up alerts on "   
CStr(box_name(y))   " with following specs"
        Else
            'Debug.Print ("Cell A2 have checkbox")
        End If
    End If
Next

CodePudding user response：

Used Application.Match() to find Alert, WorksheetFunction.CountA() to check if row is empty

Sub AlertCB()
    Const ALERT = "Alert"
    Dim rng As Range, cb As CheckBox
    
    With ActiveWorkbook.Sheets("Sheet1")
        Set rng = .Columns("A")
        Do
            m = Application.Match(ALERT, rng, 0)
            If IsError(m) Then Exit Do
            If WorksheetFunction.CountA(.Rows(m   rng.Row)) = 0 Then
                With .Cells(m   rng.Row, "A")
                    Set cb = .Parent.CheckBoxes.Add(Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
                    cb.Caption = ""
                End With
            End If
            Set rng = .Range(.Cells(m   rng.Row   1, 1), .Cells(Rows.Count, 1))
        Loop
    End With
End Sub

CodePudding user response：

Dim y As Long

For y = LBound(box_name) To UBound(box_name)
    Empty_row_A_BOOL = Range("D" & Rows.Count).End(xlUp).Offset(1).Select
    If Empty_row_A_BOOL = True Then
        Empty_row_A = Range("D" & Rows.Count).End(xlUp).Row   1
    If IsEmpty(s1.Range("A" & CStr(Empty_row_A)).Value) = True And IsEmpty(s1.Range("B" & CStr(Empty_row_A)).Value) = True Then
        s1.CHECKBOXES.Add(Left:=Range("A" & CStr(Empty_row_A)).Left, Top:=Range("A" & CStr(Empty_row_A)).Top, _
        Width:=Range("A" & CStr(Empty_row_A)).Width, Height:=Range("A" & CStr(Empty_row_A)).Height).Select
        With Selection
          .Caption = ""
        End With
        s1.Range("A" & CStr(Empty_row_A)).Offset(, 1).Value = "set up alerts on "   CStr(box_name(y))   " with following specs"
     Else
        'Debug.Print ("Cell A2 have checkbox")
     End If
     End If
Next

The code I edit seems to work properly