VBA to find missing data in column and complete the series of info

[wmmolle]

Hello, All.
I need help.

I have an excel sheet with data that pertains to product being shipped out for delivery. Most items are being shipped in multiples. I need help creating a VBA that will find these multiples and complete the series of missing numbers. as seen in the attached photo, "Item b" (found in column F) is being shipped out in a multiple of 6 (seen in cell J9). This VBA would need to find each number >1 and auto fill the leading 0s that are contained in the prior cells.

Please help. Thank you!

 

[jasonb75]

Does it need to be vba?

There is a fairly simple keyboard method that will work (I use something similar regularly).

In short, filter to show 0's, select and delete them, clear filter, goto special and select blanks, type = Down Arrow -1 into the first blank, press ctrl enter and job done.

 

[wmmolle]

unfortunately, I believe that it would need to be a VBA. each 0 is tied to different line items. sorting by 0 would remove the master quantity.

 

[jasonb75]

Filter by 0, not sort by 0. The method works perfectly well (see below). I've listed the steps that I used at the end.
When you apply a formula to a filtered range in the way that I have described, it only applies it to the visible rows, the hidden master quantities will not be changed.

Before

Book1 (version 1).xlsb
	J
1	Qty to
2	Pick
3	1
4	0
5	0
6	0
7	0
8	0
9	6
10	0
11	0
12	0
13	4
14	1
15	0
16	2
Sheet6

After

Book1 (version 1).xlsb
	J
1	Qty to
2	Pick
3	1
4	1
5	2
6	3
7	4
8	5
9	6
10	1
11	2
12	3
13	4
14	1
15	1
16	2
Sheet6

Cell Formulas
Range	Formula
J4:J8,J15,J10:J12	J4	=J5-1

Steps used to go from Before to After above. Note that 'Down Arrow' refers to the cursor key.

1. Click on J2 and apply a filter, show only rows with a 0 value.
2. Press Down Arrow once, then hold Shift and Ctrl, and press Down Arrow once more. (this will select all cells with a zero value in them).
3. Press the = key, followed by the Down Arrow, then type -1
4. Press Ctrl Enter.
5. Clear the filter.
6. (optional) admire the results.

 

[wmmolle]

Learn something new everyday! thank you soooo much. Out of curiosity, do you think that there is a way to use a VBA for this?

Thank you soooo muck.

 

[jasonb75]

Anything that can be done without vba can be done with vba, so yes, there would be a way, most likely several ways.

For what you need, I would simply mimic the steps of the manual method that I described for you, although I suspect that others would use entirely different methods.