Add new row when deleted code has been run

[ipbr21054]

Morning,

I have this code below which when i select a customer on my sheet then run it that row the customer was on is the deleted.
I find that my range P1:P50 is reduced by a row each time i run it as you would expect.
Sometimes the row to be deleted will be in between other customers.

How can i add a new row or keep my range P1:P50 all the time

Private Sub DeleteCustomer_Click()

    If ActiveCell.Column = 14 And ActiveCell.Row > 3 And ActiveCell.Value <> "" Then
        If MsgBox("ARE YOU SURE YOU WISH TO DELETE THIS CUSTOMER", vbYesNo + vbCritical, "DELETE GRASS CUTTING CUSTOMER") = vbYes Then
            ActiveCell.Resize(1, 5).Delete
        Else
           MsgBox "NO CUSTOMER WAS SELECTED", vbExclamation, "DELETE GRASS CUTTING CUSTOMER"
        End If
    End If
End Sub

 

[Marc L]

Hi,​

​

try Clear rather than Delete …​

 

[ipbr21054]

Hi,
Delete.
Will delete that row then the rows below move up one row.

Clear.
Will clear the contents of the cells but no row move up so im now looking at an empty row.

 

[Marc L]

​

So use Delete then ! As any good enough VBA procedure does not need to hardcode a range like P1:P50 …​