Delete Row when Cell Value = 0

[mjc11]

I am working with an excel spreadsheet to track all of our software, license keys, & where that software is currently installed. My workbook uses two sheets to do this, the first worksheet is named “Installed software” and the second one is called “Software”

Software Sheet:
I use this sheet to enter the software Name, Description, Version, Developer, Category, Date of Purchase, Supplier, Supplier Website, Price, License Type, Expiation /Renewal Date, Serial Number, & Quantity Purchased. I also have two fields that are calculation fields. These fields are Quantity Used & Quantity Remaining

Installed Software Sheet:
I use this sheet to enter the Name of User, a Description of Hardware, Asset Number, Date of Software Install, & Select Name of Software. Now this last field “Column G” I use a drop down field that looks at the “Software Sheet” that list all of software we have. Once I have chosen the software I am going to install from the list it changes the Calculation Field on the “Software Sheet”. i.e Quantity Used =1 & Quantity Remaining=0
What I am trying to do is automatically hide that that row on the “Software Sheet” when Quantity Remaining=0 as well as automatically update the dropdown field in Column G on the “Installed Software” Sheet so that it won’t show that software as available for install. I have been attempting to approach it from the Installed Software Sheet to hide the row on the “Software’ sheet when I select the software from the drop down list in Column G but I have not been able to get this to work

I have uploaded the sample worksheet

[skywriter]

Okay I think I've worked out a solution for you.

Go ahead and see if the sheet at the bottom of this post works the way you want before installing it on any other sheet.

What I've done is this. Like you wanted if a quantity remaining is 0 then the row gets hidden. If you remove one of your entries on the Installed Software sheet taking your quantity remaining from 0 back to a positive number the row will be visible again. The logical time to run the code was when the worksheet calculates so the code is in what's called the worksheet calculate event. Below is the code and if you need to install it in a different workbook you would need to right click on the tab for the sheet named Software and choose view code and paste the code into that sheet.

It's important if the new workbook doesn't have a table with the exact same name as the sample sheet with the columns in the exact same place and named the same, then there will be issues we will have to work through.

Please Login or Register  to view this content.

The next thing is you wanted the software to disappear from the validation list if the row was hidden.
I did this through code and with a named range. If you look under the named ranges you will see one named SoftwareValidationList.
This refers to the software names listed on Sheet1 which I added. This sheet needs to stay. You can copy it to your actual sheet but you will have to recreate the named range. Just use the formula in the named range I created and you will be fine. You can also hide this sheet it doesn't need to be visible for the code to work. What happens is the code erases the entries on this sheet and refreshes them based on which rows are hidden and which are visible.
If you look at the data validation for your drop down list on the Installed Software sheet you will see that I am listing that named range as the source for that list.

The code for the updating of the Sheet1 list is below and is installed in what is called a standard module. After you put the code above in your actual sheet per the instructions I gave you, and while you are still in that same window, click on the insert menu and choose module. Copy and paste the code below into that screen. You will need to save your actual sheet as a .xlsm macro enabled worksheet for all of this to run.

Make sure you test this on a copy of your sheet and let me know if you have any issues.

Good Luck.

Edit: I found a bug and it seemed to be caused by your Qty Used formula, the COUNTIF formula. I changed it using a Table reference.

So the formula in O8 copied down is:

Please Login or Register  to view this content.

Please Login or Register  to view this content.