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

wmmolle

New Member
Joined
Mar 10, 2021
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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!
 

Attachments

  • Capture.JPG
    Capture.JPG
    94.9 KB · Views: 5

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,242
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 10, 2021
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
13,242
Office Version
  1. 365
Platform
  1. Windows
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
1Qty to
2Pick
31
40
50
60
70
80
96
100
110
120
134
141
150
162
Sheet6

After
Book1 (version 1).xlsb
J
1Qty to
2Pick
31
41
52
63
74
85
96
101
112
123
134
141
151
162
Sheet6
Cell Formulas
RangeFormula
J4:J8,J15,J10:J12J4=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

New Member
Joined
Mar 10, 2021
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
o_O 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

Well-known Member
Joined
Dec 30, 2008
Messages
13,242
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,137,349
Messages
5,680,965
Members
419,946
Latest member
Trickay

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
Top