How do I add an "If" statement to this VBA?

[duteberta]

This DELETE ROW sub below is working but I want to add the condition that it is to only delete the row when a "1" value in the first table column (called data[x]). How do I syntax this to the code?

Sub DeleteRow()
    
    Dim question As String
    Dim rowNumber As Long
    
        rowNumber = ActiveCell.Row
    
    
        question = "Are you sure" & "?"

        If MsgBox(question, vbOKCancel + vbCritical + vbDefaultButton2, "DELETE RECORD") = vbOK Then
                
                Rows(rowNumber).EntireRow.Delete
        
                Else
            
            Exit Sub
        
        End If

End Sub

 

[Cubist]

Maybe this. Not tested. Try on a copy.

Sub DeleteRow()
    Dim question As String
    Dim rowNumber As Long

    rowNumber = ActiveCell.Row

    If Range("data[x]").Cells(rowNumber).Value = 1 Then
        question = "Are you sure you want to delete this record?"
        If MsgBox(question, vbOKCancel + vbCritical + vbDefaultButton2, "DELETE RECORD") = vbOK Then
            Rows(rowNumber).EntireRow.Delete
        Else
            Exit Sub
        End If
    Else
        MsgBox "You can only delete rows with a value of '1' in the first column.", vbExclamation, "Invalid Deletion"
    End If
End Sub

 

[duteberta]

Maybe this. Not tested. Try on a copy.

Sub DeleteRow()
    Dim question As String
    Dim rowNumber As Long

    rowNumber = ActiveCell.Row

    If Range("data[x]").Cells(rowNumber).Value = 1 Then
        question = "Are you sure you want to delete this record?"
        If MsgBox(question, vbOKCancel + vbCritical + vbDefaultButton2, "DELETE RECORD") = vbOK Then
            Rows(rowNumber).EntireRow.Delete
        Else
            Exit Sub
        End If
    Else
        MsgBox "You can only delete rows with a value of '1' in the first column.", vbExclamation, "Invalid Deletion"
    End If
End Sub

Unfortunately this did not work

 

[Cubist]

Does your table start in row 1? Row 1 of Excel might not be row 1 of your table.
Edit: Better yet, why not just delete all rows with "1" instead of just the active row?

 

[duteberta]

Does your table start in row 1? Row 1 of Excel might not be row 1 of your table.

Row 1 is always in the first row of the table (not the sheet).

 

[duteberta]

https://greenpocketrealtyllc-my.sha...JHl1xgROUF79wBLsiTfedAbCS0h6RSCUbtRw?e=l2fENr

 

[Cubist]

Row 1 is always in the first row of the table (not the sheet).

See the example below. When you call rowNum = ActiveCell.Row. This is row 5 of Excel, but the row of your table is 4. Better yet, why not just delete all rows with "1" instead of just the active row?
You've shared a linked to your internal sharepoint, can't access.

In

 

[duteberta]

Ok thanks. I'll try to figure out sharing sheets. In my table I have it setup so that only one row can have a "1" in the first column for a myriad of other calculations. I only need the "DELETE ROW" macro to delete the 1 table row that has the "1" in it.

 

[Cubist]

This works for me. See if this works for you.

Sub DeleteRowsWithValueOne()
    Dim question As String
    Dim dataTable As ListObject
    Dim ws As Worksheet
    Dim i As Long
    Dim rowsToDelete As Range
    Set rowsToDelete = Nothing
   
    ' Assuming you want to work with the active sheet
    Set ws = ActiveSheet
   
    ' Assuming "Data" is the name of your table
    Set dataTable = ws.ListObjects("Data")
   
    Application.ScreenUpdating = False
   
    ' Loop through each row in the table
    For i = dataTable.ListRows.Count To 1 Step -1 ' Looping backwards to avoid issues with deleting rows
        ' Check if the value in column "x" of the current row is 1
        If dataTable.DataBodyRange.Cells(i, dataTable.ListColumns("x").Index).Value = 1 Then
            ' Add the entire row to the range to be deleted
            If rowsToDelete Is Nothing Then
                Set rowsToDelete = dataTable.ListRows(i).Range
            Else
                Set rowsToDelete = Union(rowsToDelete, dataTable.ListRows(i).Range)
            End If
        End If
    Next i
    ' Prompt the user to confirm deletion
    If Not rowsToDelete Is Nothing Then
        question = "Are you sure you want to delete?"
        If MsgBox(question, vbOKCancel + vbCritical + vbDefaultButton2, "DELETE RECORDS") = vbOK Then
            ' Delete all rows at once
            rowsToDelete.Delete
        End If
    End If
    Application.ScreenUpdating = True 
End Sub

 

[duteberta]

Dang- I wish I could figure things out as fast as you!! Great job! Works perfectly. I really appreciate it.