Move up other cell values one row after a row deletion code has been run

[ipbr21054]

Morning,

I have the working code supplied below but wish to add some code or get advice for adding some extra code to it.

On my worksheet called G INCOME i have a section which contains some information regarding my customers,i have attached a screen shot for you.
The first entry will always be N4

So the code will delete a customers entry lets say in this case ISABEL of which is at row 13
It deletes it fine but this leaves row 13 empty.
The advice i need would be to copy / paste the entries below & move them up 1 row.

Currently im having to manualy do this each time.

For your information i also have other info on the worksheet so we need to keep within the range of this otherwise that rows info will also be deleted / altered etc
Thanks.

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

 

[mumps]

Try:

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

 

[ipbr21054]

Spot on thanks,real neat

 

[mumps]

You are very welcome.

 

[ipbr21054]

I spoke to soon as just noticed something.

Column A i enter the date.
Column B i select a customer from a drop down list
Column C pulls in the address & has the code =IFERROR(VLOOKUP($B12,$N:$DC,2,FALSE),"")
Column D pulls in a price & has the code =IFERROR(VLOOKUP($B12,$N:$DC,4,FALSE),"")
Column E pulls in the mileage distance & has the code =IFERROR(VLOOKUP($B12,$N:$DC,5,FALSE),"")

The code pulls in the answers from the table we just altered.

Ive just gone to select a customer in column B BUT the last name is the list is NORMA F where is the list we just altered there are another 4 customers shown BUT there are not now in the drop down list ?

Did i explain the correct for you

 

[mumps]

I think that it would be easier to understand and help if you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[ipbr21054]

Here we go

click here

 

[ipbr21054]

See attached screenshots

In yellow list look at last 4-5 names

Look in drop down list in column B & you will see that the selectable names stop short of what youve just looked at .

 

[mumps]

Your link doesn't work.

 

[ipbr21054]

Strange.

They this

CLICK TO DOWNLOAD