VBA Add and Delete Rows Help

[G-Matriix]

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!

 

[erik.van.geit]

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

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]

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]

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]

An example to get you started:

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]

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]

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]

assuming you want to check sheet1 column1

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]

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 $JAP 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]

 

[erik.van.geit]

did you see my reply ?