[VBA] Delete rows under certain conditions

Thompsonnl

New Member
Joined
Sep 27, 2018
Messages
6
I have a sheet (made by some SQL/VBA/Excel expert long time ago). In this sheet I can enter a production run number ("Productieorder / PO") and it will show me all necessary components with current stock, clearance status, total needed stock etc. pulled form an insanely large database.
I would like to make this list easier to work with with the click of a button. But unfortunately I am far from a SQL/VBA/Excel expert.

Because images say more than a thousand words;

GMHiWMx.png


Explanation;
Article - Mentions all the needed articles for this PO (can be up to 70 different ones from a total of 1000, all with different codes)
Needed - Shows the needed amount for this PO
Available - Shows stock of said article/batch combo
Batch - Shows the corresponding batch of said article
Blocked - Yes or No
Total need - Total needed amount of this article for all open production runs, not just the one checking right now


What I would like to do/2 examples;

The PO I am checking needs 792 grams of GR00100.
I have a total of 6 batches available for a total of 266.179 grams, none of which are blocked.
My total need for all open PO's is 80.401 grams.
The total need is covered by the total available stock.
Hide all GR00100.

The PO I am checking needs needs 2970 grams OF GR00926
I have a total of 3 batches available for a total of 555.500 grams, of which 550.000 grams is blocked.
My total need for all open PO's is 15.539 grams
The total need is _not_ covered by the total available stock (since 550.000 grams is blocked)
Show all GR00926.

So if total need > total available (not blocked) I do want to keep these rows visible.

If I am not mistaken, the process should be as following;
1) Hide all Blocked:Yes rows.
2) Identify the rows belonging to 1 article (for example GR00100 - Row 5~10)
3) Add total Available stock for Row 5~10
4) Compare total Available stock for Row 5~10 to total need
5) Mark GR00100 as + (or - in case of not meeting the requirements)
6) Repeat steps 2~4 for every unique article
7) Show all Blocked:Yes rows.
8) Hide all "+" marked article

I can make a new button which performs step 1. But unfortunately that is where my skills end.
Hoping you can point me in the right direction on how to approach improving this worksheet :)
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Another bump. I know it is a rather extensive question but some pointers in the right direction would already help!
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

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
Back
Top