Excel-Macro to delete all rows with a date entered on multiple tabs

[Seirith]

Hello, I am trying to do something and I have no idea how to set it up or if it can be done.

I am using Excel to make a inventory system for my parents who have a small retail antique/collectibles business. They have different categories of items, which I am making in tabs. Each line has their inventory number, item description, price they paid for the item, price they sold it for and then the date it sold.

Is it possible to make an end of the year macro to delete any row that has something in the date column? In essence, I want everything that sold in 20017 to be deleted and everything that has not sold to stay which will make the beginning inventory for 2018.

Is this possible since I really have no idea how many items on each tab will be sold and remain? I would love if it I could do it all at once across all tabs, but if it is only possible with 1 tab and have a macro for each tab that is fine too.

If anyone could help out on how I could go about doing this, I would really appreciate it! Thank you!

 

[Peter h]

Here you go. Copy and Paste this code into a general Module. I created a button on one of the worksheets and assigned it the macro. This is assuming that your date is in Column E. Make sure to test this code in a copy of your workbook as to not destroy any data before verifying that it works the way you're hoping.

Sub DelRow()
Dim ws As Worksheet
Dim lRow As Long
Dim i As Integer
For Each ws In ThisWorkbook.Worksheets
lRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lRow
        If ws.Cells(i, 5) <> "" Then
            ws.Cells(i, 5).EntireRow.Delete (xlShiftUp)
            i = i - 1
        End If
    Next i
Next ws
End Sub

 

[Seirith]

Hi Peter,

Thank you so much for helping. I am excel challenged and am unsure of if I am putting the code in the right place because I cannot get it to work. I am using 2007 if that makes a difference but I right now do have the trial for Office365.

In order to put it in, I went to view code, copied what you put into the workbook tab but when I picked run macro it gives me a 400 error. Am I putting the code in the right place or am I totally wrong?

I also moved the date from E to G per my parents request.

Thanks for any other help you could give!

 

[Peter h]

Go back to where you viewed the code and pasted it to. You're going to have to erase what you pasted. From that screen, up at the top go to Insert > module. Double click on the new module that is in the left side of the screen. That is where you will paste the code. So then on the actual worksheet and insert a button, and assign it the macro. I'm away from my computer, but I'll try to be as helpful as I can. If you need any more help, let me know.

 

[Seirith]

I went back and added a module, added the code to it,then made a button and assigned the macro. When I try to run it I get the error "run-time error '1004'-Delete method of Range class failed. When I hit debug, it highlights the line:

ws.Cells(i, 5).EntireRow.Delete (xlShiftUp)

Is that because I moved the date from E to G?

This is how the book is currently set up, I will be adding more tabs with categories, but the A-G headings won't be changing. I made it a link since the image is quite large. Thanks again for all the help!

http://i.imgur.com/zWO3Smz.jpg

 

[Peter h]

The 5 is the column reference that you said had the Year data in. If you moved the data from that column to column G, then you need to change the code to reference the new column, which would be 7. You'll have to change both instances in the code from 5 to 7. Let me know if that fixes your error.

 

[Seirith]

I think I got it to work! I updated your 5 to a 7 since I changed the date location and changed i=1 to i=4 since the 4th row is where my data starts and it worked. I will keep filling in more data on different sheets and test it!

One other question, once that macro is pushed, can what it does be undone in any way? Once I pushed the button the undo is greyed out. I am just worried that if the button got accidentally got pushed and it can't be undone, that would be very bad.

 

[Peter h]

No, our can't be undone. The thing about macros is that you can't use the undo button. What we can do is make a message box that pops up and asks the user to confirm the action.

msg1 = msgbox("You are about to delete all items that have been sold. Are you sure that's what you want to do? " & vbNewLine & "Yes to continue, or no to cancel. ", vbYesNo)
If msg1 = vbNo Then Exit Sub

Try putting that before the "for I =" line. I'm just on my phone and away from my computer, so if it causes an error or anything let me know and I'll fix it.

 

[Seirith]

That worked great, thank you so much for all your help! I really appreciate it.

 

[Peter h]

That worked great, thank you so much for all your help! I really appreciate it.

glad I can help. If you need any more help, don't hesitate to ask.