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

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,321
Office Version
  1. 2007
Platform
  1. Windows
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.


Rich (BB code):
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
 

Attachments

  • 2085.jpg
    2085.jpg
    132.5 KB · Views: 4

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,148
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Try:
VBA Code:
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
 
Solution

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,148
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
You are very welcome. :)
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,321
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Apr 11, 2012
Messages
12,148
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
4,321
Office Version
  1. 2007
Platform
  1. Windows
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 .
 

Attachments

  • 2086.jpg
    2086.jpg
    125.3 KB · Views: 3
  • 2087.jpg
    2087.jpg
    67.1 KB · Views: 4

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,148
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Your link doesn't work.
 

Forum statistics

Threads
1,176,042
Messages
5,901,092
Members
434,870
Latest member
michud08

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top