VBA Add and Delete Rows Help

G-Matriix

Board Regular
Joined
Sep 20, 2006
Messages
139
Hello Everyone,

I Need alittle help with adding and deleting rows in excel using a button. Basically, I need it to add or delete a name and their initials from one sheet and on another sheet add or delete entire row. Is there a macro I could use to accomplish this?

Please Help!

Thanks!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, G-Matriix
Welcome to the Board !

to know the code to add or delete rows, you can check out the helpfiles or record a macro doing your operations
Code:
Rows("1:1").Delete
"or just
Rows(1).Delete
but your question does probably mean more than that: care to elaborate

kind regards,
Erik
 

G-Matriix

Board Regular
Joined
Sep 20, 2006
Messages
139
Yes, Thank You!

What I am trying to do is....I have a list of employees. I am tracking how many job they do throughout the month. When a person quits or is hired I would like to add or delete a row on another sheet where there stats are. This way my other formulas will not mess up (#REF!).

Please help and Thanks Again!
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
for this kinda operations an example would be useful
care to post some sampledata and some prodecure-steps

take a look at FIND in the helpfiles
there is a nice example

find name (how do you want to provide the name to the code ?)
if found delete row
perhaps: if not found add row ?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

An example to get you started:

Code:
Sub test()
Dim Myvalue As String, Found As Range
    Myvalue = "VoG"
    Set Found = Sheets("MySheet").Columns(2).Find(Myvalue)
    If Found Is Nothing Then
        Beep
        Exit Sub
    End If
    Found.EntireRow.Delete
End Sub
 

G-Matriix

Board Regular
Joined
Sep 20, 2006
Messages
139
Yes, simular to your questions.... It will be two different buttons. One for adding a name (entire row) ....and one for deleting a name (entire row). I would just assign the macro for adding a row to the "Add" button and the other macro to the "Delete" button

Add Button: If value (cell ref) is not not found then add a row at the bottom of list with the name in the very far left

Delete Button: If value (cell ref) is found then delete entire row

somthing like a VLooup Function

Thanks Again!
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

There's all sorts of information missing here! Where is the data, what is missing, the add/delete criteria and so on.

Can you post your workbook and what you hope to achieve?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
assuming you want to check sheet1 column1
Code:
Option Explicit

Sub add_employee()
Dim employee As Variant
Dim foundit As Range

    employee = Application.InputBox("Please type the name of the employee", "EMPLOYEE", "")
    If employee = "" Or employee = False Then Exit Sub

    With Sheets(1)
    Set foundit = .Columns(1).Find(employee, lookat:=xlWhole, MatchCase:=False)

        If foundit Is Nothing Then
        .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = employee
        Exit Sub
        Else
        MsgBox "Employee already exists", 48, "ERROR"
        End If

    End With

End Sub

Sub delete_employee()
Dim employee As Variant
Dim foundit As Range

'NOTE: you can click within your sheet to pick the employee to delete
    employee = Application.InputBox("Please type the name of the employee", "EMPLOYEE", "")
    If employee = "" Or employee = False Then Exit Sub

    With Sheets(1)
    Set foundit = .Columns(1).Find(employee, lookat:=xlWhole, MatchCase:=False)
    

        If foundit Is Nothing Then
        MsgBox employee & " not found", 48, "ERROR"
        Exit Sub
        Else
        If MsgBox("Do you want to delete " & employee & "?", 292, "DELETE") = vbYes Then foundit.EntireRow.Delete
        End If

    End With

End Sub

I would prefer a combobox or listbox to pick the employee to delete

kind regards,
Erik
 

G-Matriix

Board Regular
Joined
Sep 20, 2006
Messages
139
Ok sorry for getting back so late......

EDI Purple Initials 1-Aug 2-Aug 3-Aug 4-Aug 5-Aug 6-Aug 7-Aug 8-Aug 9-Aug 10-Aug 11-Aug 12-Aug 13-Aug 14-Aug 15-Aug 16-Aug 17-Aug 18-Aug 19-Aug 20-Aug 21-Aug 22-Aug 23-Aug 24-Aug 25-Aug 26-Aug 27-Aug 28-Aug 29-Aug 30-Aug 31-Aug
Alethea Lawson $ALL 25 11 0 3 1 0 11 26 17 7 7 5 0 16 15 11 12 17 1 0 11 11 15 15 15 4 0 9 15 6 8
Alice Trischler $AMT 14 10 10 8 0 0 12 18 12 0 0 0 0 0 0 16 10 8 0 0 13 6 7 0 13 0 0 8 12 7 12
Amanda Black $AJB 0 3 2 1 5 0 0 0 6 0 6 4 0 0 0 4 6 5 5 0 0 0 5 0 5 4 0 0 0 3 1
Amanda Tracey $AAT 12 0 0 4 0 0 9 10 0 15 22 0 0 8 17 0 8 10 0 0 14 6 0 8 5 0 0 2 7 0 7
Andre Warnsley $ARW 2 2 0 1 0 0 0 4 1 3 0 0 0 0 8 1 2 4 2 0 0 1 2 0 7 0 0 0 0 10 2
Andrea Bogenschutz $AMY 5 0 0 1 0 0 2 6 0 0 1 0 0 1 1 0 0 5 0 0 11 2 0 3 3 0 0 1 4 0 11
Angela Harris $AFH 10 0 5 13 0 0 8 7 0 15 17 0 0 4 11 0 10 9 0 0 18 8 0 10 9 0 0 9 11 0 10
Angel Tadeo $ANT 4 7 25 2 0 0 11 4 2 3 0 0 0 13 6 3 10 7 0 0 5 6 0 0 0 0 0 0 0 0 0
Angie Gregg $AMG 11 7 14 10 0 0 7 3 3 4 7 0 0 6 18 4 9 3 0 0 9 8 0 7 7 0 0 4 4 4 6
Anthony Brown $ADB 0 0 1 0 0 2 0 0 0 0 1 1 0 1 0 0 0 8 6 1 0 0 0 3 3 8 0 0 0 0 5
Ashleigh Paxson $ANP 16 12 8 15 0 0 5 8 0 0 0 0 0 0 9 6 8 8 0 0 10 5 0 8 5 5 0 2 12 6 3
Ben Schaab $BAS 5 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Beth Gadbois $BAG 0 0 0 2 4 1 2 0 0 0 3 2 3 1 0 0 0 8 1 5 5 0 0 0 9 6 0 7 0 0 0
Billie Mitchell $BFM 14 15 18 1 0 0 8 9 15 15 0 0 0 14 24 13 20 0 0 0 12 11 9 10 0 0 0 9 13 10 20
Bradley Gerig $BAG 1 2 2 1 0 0 2 6 5 3 3 0 0 3 9 0 4 2 0 0 6 3 3 4 2 0 0 1 4 4 3
Bruce Osborn $HBO 2 7 4 8 0 0 0 8 3 3 0 0 0 4 9 13 5 21 0 0 8 11 12 6 9 0 0 8 15 8 15
Carnes Gary $GMC 0 1 1 1 0 0 4 2 1 0 0 0 0 4 0 1 0 0 0 0 0 0 0 0 0 0 0 1 2 3 0
Carol Jetmore $CEJ 0 0 6 8 0 1 0 0 0 8 4 3 0 0 0 0 2 2 1 3 0 0 0 4 2 3 3 0 0 0 1
Caryn Freeman $CGF 8 0 2 3 2 0 0 4 0 3 3 6 0 0 8 2 1 2 13 0 0 10 9 5 5 3 0 0 11 4 4
Catherine Wheaton $CLW 3 1 4 0 0 3 8 4 5 3 0 0 2 4 13 2 2 0 0 1 8 3 11 0 0 0 1 1 13 10 2
Cheryl Roberts $CLR 8 16 15 29 0 0 14 0 13 14 20 0 0 7 21 12 11 20 0 0 0 0 0 0 0 0 0 0 0 0 0
Chris Golden $CRG 0 0 2 0 0 0 0 0 0 0 0 0 0 0 0 6 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Darlene Zuber $DSZ 0 0 14 13 6 1 0 0 0 17 17 0 4 0 0 0 7 11 10 4 0 0 0 15 8 4 8 0 0 0 12
Dave Hottman $DAH 3 0 0 0 2 1 9 6 0 0 0 3 3 9 8 1 6 0 4 0 8 8 0 0 0 0 2 9 4 0 0
David Leshore $DLL 0 0 0 0 0 0 13 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Deanna Lahmon $DL 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 1 0 0 0 1 0 0 0 0 0 0 0 1 0 0
Demires Triggs $DNT 2 0 0 0 3 3 2 3 0 0 0 2 1 3 4 0 0 0 2 4 6 3 2 0 0 0 0 1 5 0 0
Diane Puff $DRP 18 17 9 18 0 0 11 15 10 9 15 0 0 18 21 16 21 17 0 0 23 11 19 18 20 0 0 17 16 2 16
Emma Robinette $ECR 14 10 11 0 0 0 34 17 15 16 0 0 0 16 25 13 15 0 0 0 14 12 41 19 0 0 0 19 20 18 0
Glenys Armbrust $GLA 0 0 0 5 3 3 11 1 0 0 9 6 8 5 0 0 0 0 4 8 4 0 0 0 9 6 4 4 0 0 0
Holly Sass $HLS 18 16 0 0 0 3 9 19 17 0 0 0 190 9 14 0 0 0 0 0 0 3 8 0 0 0 12 10 16 13 0
Jami Burns $JAB 13 4 4 7 0 0 8 2 5 3 7 0 0 4 15 7 9 14 0 0 15 4 5 9 11 0 0 4 8 10 13
Janet Willison $JLW 2 0 2 0 0 0 0 1 0 1 3 0 0 0 0 0 2 1 0 0 0 0 1 3 0 0 1 0 0 0 1
Jason Powell $JJP 0 5 3 0 0 0 0 2 7 0 0 0 0 0 0 0 0 0 0 0 7 9 2 2 0 0 0 2 2 12 6
Jayme Cashore $JSC 4 0 0 1 0 0 0 0 0 0 1 0 0 0 2 0 0 4 0 0 0 6 0 0 5 0 0 0 0 0 1
Jeremy Jacobs $JJJ 5 4 9 8 1 0 7 5 3 4 0 0 0 0 0 4 4 5 0 0 8 3 4 8 2 0 0 2 9 9 9
Jerry Baughman $JOB 7 7 14 11 4 0 0 5 14 5 12 4 0 0 7 8 9 4 13 0 0 2 7 6 0 4 0 0 0 0 0
Jessica Helton $JMH 8 6 8 2 8 0 4 12 9 5 1 0 0 1 12 7 8 1 2 0 1 6 13 5 9 1 0 3 4 1 3
Jessica Winston $JLW 8 2 0 0 0 4 2 3 0 0 0 0 1 1 0 0 0 0 0 0 0 2 0 0 2 0 0 8 5 3 0
Joan Reynolds $JLR 1 2 4 2 0 0 2 2 1 1 3 0 0 1 4 1 1 2 0 0 0 0 1 6 2 0 0 3 4 7 5
Josh Kissinger $JJK 3 10 7 0 0 0 3 1 7 5 0 0 0 3 9 6 2 0 0 0 10 5 8 6 0 0 0 4 10 18 5
Joyce Light $JLL 0 0 4 2 1 2 0 0 0 2 1 3 0 0 0 0 3 2 4 1 0 0 0 9 2 0 3 0 0 0 5
Juattassa Brown $JRB 6 0 0 4 1 0 0 0 0 2 0 0 0 0 0 0 4 0 1 0 0 0 0 0 0 0 0 0 0 0 3
Julie Payne $*** 0 5 7 4 6 3 0 1 4 5 3 5 3 0 2 3 5 6 1 0 0 1 5 8 3 6 2 0 0 8 4
Julius Stevens $JJS 0 0 0 1 0 0 5 0 0 0 2 1 1 5 0 0 0 1 2 7 4 0 0 0 3 4 3 8 0 0 0
Justin Wolfe $JMW 0 4 5 6 0 0 0 0 9 9 2 0 1 0 0 0 0 11 7 0 0 0 8 2 4 0 0 0 0 3 7
Kadesha Brown $KAB 11 13 11 7 0 0 4 6 2 6 8 0 0 6 16 8 14 11 0 0 10 4 7 5 4 0 0 4 8 7 6
Katrina Amos $KLA 1 4 2 2 0 0 5 0 1 9 0 0 0 4 8 1 2 0 0 0 9 5 1 3 0 0 0 2 6 2 4
Kelly Lewis $KJL 1 0 1 0 0 2 2 5 1 0 0 0 1 3 7 3 0 0 0 0 5 3 16 4 0 0 0 3 2 9 0
Kristen Turney $KMT 0 7 0 16 8 7 20 0 0 0 23 0 11 20 0 0 0 17 10 5 13 0 6 0 16 3 4 17 9 0 0
Leslie Rudolph $LAR 6 1 0 0 0 1 0 0 0 0 0 0 2 4 12 4 0 0 0 3 11 3 0 0 0 0 0 0 3 3 0
Mikki Caulder $MJC 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Natalie Johnson $NMJ 0 0 0 9 4 3 8 0 4 0 10 2 3 14 0 0 4 7 7 1 22 0 0 0 19 0 4 10 0 0 0
Nicole Gibson $NLG 0 0 0 0 0 1 0 0 0 0 12 1 3 0 0 0 0 3 5 0 0 0 0 0 7 6 1 0 0 0 0
Pat Eberle $PLE 0 0 0 0 3 0 0 17 18 16 0 0 0 21 30 12 31 15 0 0 10 3 8 8 0 0 0 7 16 10 12
Patricia McGown $PMC 7 3 4 8 0 0 7 7 2 8 4 0 0 7 12 2 1 4 0 0 17 11 9 11 5 0 0 7 14 8 12
Patty Bellis $PAB 7 3 5 5 0 0 0 0 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Peggy Bates $PMB 16 23 29 33 0 0 18 21 26 17 18 0 0 24 20 18 45 18 0 0 20 21 20 21 17 0 0 23 19 19 28
Penny Hoskins $PSH 0 0 0 22 25 13 0 0 0 0 22 31 26 0 0 0 0 14 9 8 0 0 15 0 19 14 14 0 0 8 0
Rachel Tomlinson $RDT 0 6 0 0 3 0 7 0 0 0 0 0 0 13 0 0 0 0 0 0 0 0 3 0 1 1 6 6 0 2 0
Randy Smith $RNS 18 0 15 13 0 0 13 19 22 19 20 0 0 16 19 4 0 0 0 0 12 16 17 16 15 0 0 22 17 13 19
Rebecca Cunningham $RAC 16 20 25 19 0 0 14 12 26 10 17 0 0 9 24 12 6 19 0 0 19 17 19 10 11 0 0 15 16 18 21
Regan Pierce $RAP 16 18 27 10 4 0 0 0 0 0 0 0 0 0 26 13 28 24 15 0 0 15 20 22 19 11 0 0 21 18 32
Robert Washington $RHW 0 2 2 12 3 0 0 0 1 7 6 1 0 0 0 1 0 0 1 0 0 0 5 3 5 1 0 0 0 1 4
Sandy Mignerey $SKM 11 11 16 0 0 0 0 22 25 26 1 0 0 25 1 15 29 0 0 0 14 25 14 12 1 0 0 17 23 15 28
Sean Stewart $SJS 0 0 3 0 1 1 3 0 0 2 1 2 0 5 0 0 2 1 4 1 7 0 0 0 0 1 3 6 0 0 4
Sharon Johnson $SAJ 4 24 18 0 0 0 0 4 18 18 18 0 0 0 0 8 12 0 1 0 15 0 16 13 0 0 6 1 0 14 12
Sherryl McCullough $SAM 9 0 0 0 0 0 0 15 6 0 0 0 0 13 18 17 20 26 0 0 26 16 17 9 21 0 0 6 18 16 14
Smith Christopher $CES 20 33 13 0 0 0 10 1 3 4 0 0 0 2 32 13 18 0 0 0 3 4 6 3 0 0 0 1 0 1 0
Stephanie Benzinger $SWB 0 2 7 11 8 2 0 0 1 7 0 8 0 0 0 0 7 10 10 7 0 0 7 10 6 3 10 0 0 4 10
Steve Nahrwold $SLN 13 22 2 0 0 1 10 19 15 0 0 0 12 18 21 20 3 0 0 2 16 23 20 2 0 0 3 34 28 16 2
Talaya Allen $TLA 16 20 0 6 6 3 16 17 20 0 0 0 0 6 35 8 4 0 0 7 9 2 3 0 0 0 0 14 21 12 0
Tim Boger $TWB 10 4 6 5 2 0 3 3 2 4 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Tina Papoli $TRP 0 0 2 2 0 0 0 1 0 1 2 0 0 1 12 3 1 0 0 0 3 0 5 0 0 0 0 1 2 2 3


*This an example of the a list of employees with their initials and the jobs completed.

*What I would like to do is on one sheet have the list of names here and each time someone leave the company I select their name and click delete and it deletes the entire row of the that person

*If I click the add button it add an entire row so that the data can be entered for that person.

Criteria for add and delete buttons

Add Button: If value (cell ref) is not not found then add a row at the bottom of list with the name in the very far left

Delete Button: If value (cell ref) is found then delete entire row

somthing like a VLooup Function

[/list]
 

Forum statistics

Threads
1,144,370
Messages
5,723,960
Members
422,529
Latest member
mbilal429

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