Delete row advice please

[ipbr21054]

Hi,
My goal is to select a customer in column A & delete entire row but stuck on the code to answer the question of Do You Wish To Delete Customers Row

I first run the code to make sure a Customer in column A is selected.
If i select a cell in column B i see the Msgbox advising to select column A so that works fine.
If i select a cell in column A this also works fine & the codes then continues & i see the msgbox asking do i wish to delete customers row.
This is where things now go wrong for me.

The black text worked fine on its own BUT when i add the Red text code the msgboxes are out of sync etc

Private Sub CommandButton3_Click()
Dim answer As Integer

If ActiveCell.Column = 1 Then
answer = MsgBox("DELETE CUSTOMERS ROW ?", vbYesNo + vbCritical)
If answer = vbYes Then
EntireRow.Delete
Else
MsgBox "YOU NEED TO SELECT A CUSTOMER IN COLUMN A", vbCritical, "REPEAT CUSTOMER MESSAGE"
End If
End If

End Sub

 

[Joe4]

You need to tell it which row to delete (you have not specified any range in your delete line).
So you need to change this line:

EntireRow.Delete

to something like this:

ActiveCell.EntireRow.Delete

 

[ipbr21054]

HI,
Thanks the code i now have is shown below.
The operation is now out of sync.

Select column B & run command button but nothing happens.

Select column A i see msgbox Delete customers row so i select YES & row is deleted.

Selting No then shows msgbox You need to select customer in column A

Private Sub CommandButton3_Click()
Dim answer As Integer

If ActiveCell.Column = 1 Then
answer = MsgBox("DELETE CUSTOMERS ROW ?", vbYesNo + vbCritical)
If answer = vbYes Then
ActiveCell.EntireRow.Delete
Else
MsgBox "YOU NEED TO SELECT A CUSTOMER IN COLUMN A", vbCritical, "REPEAT CUSTOMER MESSAGE"
End If
End If

End Sub

 

[Joe4]

You put your ELSE statement in the wrong IF loop.
If you properly indent your code, this becomes much more obvious to see.

Your code should look like this:

Private Sub CommandButton3_Click()
   
    Dim answer As Integer

    If ActiveCell.Column = 1 Then
        answer = MsgBox("DELETE CUSTOMERS ROW ?", vbYesNo + vbCritical)
        If answer = vbYes Then
            ActiveCell.EntireRow.Delete
        End If
    Else
        MsgBox "YOU NEED TO SELECT A CUSTOMER IN COLUMN A", vbCritical, "REPEAT CUSTOMER MESSAGE"
    End If

End Sub

 

[ipbr21054]

Thanks now working.

Moving the last END IF & putting it before the Else was the answer correct ?
Thanks the difference i see

 

[Joe4]

Thanks now working.

Moving the last END IF & putting it before the Else was the answer correct ?
Thanks the difference i see

Yes, but do you understand why?
The "Else" you want goes with your first IF, which is checking the column, not the second "IF", which is checking the answer to the question.
So because of nesting, you need to close out the second IF before entering the "Else" clause.

If that is confusing, build it one IF clause at a time, i.e. your first one would look like this:

    If ActiveCell.Column = 1 Then
        answer = MsgBox("DELETE CUSTOMERS ROW ?", vbYesNo + vbCritical)
    Else
        MsgBox "YOU NEED TO SELECT A CUSTOMER IN COLUMN A", vbCritical, "REPEAT CUSTOMER MESSAGE"
    End If

which says, if the column is the first column, ask your delete question, otherwise tell them they need to select column A.

Now you want to add your code of what to do with the Delete question, i.e.

    If ActiveCell.Column = 1 Then
        answer = MsgBox("DELETE CUSTOMERS ROW ?", vbYesNo + vbCritical)
        'insert new code here after the question
    Else
        MsgBox "YOU NEED TO SELECT A CUSTOMER IN COLUMN A", vbCritical, "REPEAT CUSTOMER MESSAGE"
    End If

so it becomes this:

    If ActiveCell.Column = 1 Then
        answer = MsgBox("DELETE CUSTOMERS ROW ?", vbYesNo + vbCritical)
        If answer = vbYes Then
            ActiveCell.EntireRow.Delete
        End If
    Else
        MsgBox "YOU NEED TO SELECT A CUSTOMER IN COLUMN A", vbCritical, "REPEAT CUSTOMER MESSAGE"
    End If

So I would make two recommendations for you when building code like this:
1. Always indent your code in outline form like I have shown above. It makes it easier to follow/read and to see what is going on.
2. Do not try to write it all at once. Build it like I showed above, starting with the first IF, and then adding the second IF in the correct location afterwards.

 

[ipbr21054]

Yes I now understand.
With my original it was going through the process start to finish.
So you can have multiples of
If & End If followed by Else in between each

Thanks.