I have a macro I'd like to develop...
We have a weekly invoice that comes in from a supplier and invariably there are many lines where the cell in the final column contains 0.00 to denote no value= no stock sent.
We tend to group any rows where no stock has come all together at the top and colour them red. Sorting doesn't really matter here, we don't column sort.
After that, though, we'd like to sort any column where the value isn't 0.00 by column 'A'.
Additionally (bonus points!) we then insert a column of tickboxes but I've never worked out how the script can calculate how many rows there are and fill down the tickboxes automatically like double clicking the fill down symbol does in regular use of Excel
many thanks in advance, sample data below, but obviously the regular spreadsheet has many more rows.
We have a weekly invoice that comes in from a supplier and invariably there are many lines where the cell in the final column contains 0.00 to denote no value= no stock sent.
We tend to group any rows where no stock has come all together at the top and colour them red. Sorting doesn't really matter here, we don't column sort.
After that, though, we'd like to sort any column where the value isn't 0.00 by column 'A'.
Additionally (bonus points!) we then insert a column of tickboxes but I've never worked out how the script can calculate how many rows there are and fill down the tickboxes automatically like double clicking the fill down symbol does in regular use of Excel
many thanks in advance, sample data below, but obviously the regular spreadsheet has many more rows.
| Invoice_391195_20210409.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | Code | Product Code | Brand | Chkbox | Product | Type | Qty | Pack | Singles | RRP | VAT | Net Price | Net Total | ||
| 2 | 739426 | 2515P0000 | Hozelock | ? | Vortex Multi Sprinkler | Sale | 0 | 0 | 0 | 25.29 | 20.00% | 0 | 0 | ||
| 3 | 230246 | SHPR30K | Garden | ? | Hose & Spray Nozzle Set | Sale | 0 | 0 | 0 | 24.99 | 20.00% | 0 | 0 | ||
| 4 | 267820 | 2292P9000 | Hozelock | ? | Hose Nozzle | Sale | 0 | 0 | 0 | 9.2 | 20.00% | 0 | 0 | ||
| 5 | 822119 | 2050P0025 | Hozelock | ? | Hose End Connector Twin Pack | Sale | 1 | 10 | 10 | 11.99 | 20.00% | 49.81 | 49.81 | ||
| 6 | 226622 | 2976P0000 | Hozelock | ? | Rectangular Sprinkler Pro | Sale | 2 | 1 | 2 | 68.49 | 20.00% | 23.59 | 47.18 | ||
| 7 | 267230 | 2075 0000 | Hozelock | ? | Waterstop Connector Standard | Sale | 1 | 10 | 10 | 7.25 | 20.00% | 28.5 | 28.5 | ||
| 8 | 226621 | 2335P0000 | Hozelock | ? | Round Sprinkler Pro 2N1 | Sale | 2 | 1 | 2 | 41.19 | 20.00% | 13.8 | 27.6 | ||
| 9 | 187010 | 7215P9000 | Hozelock | ? | Starter Hose & Fitting Set | Sale | 2 | 1 | 2 | 43.49 | 20.00% | 13.71 | 27.42 | ||
| 10 | 827961 | 2294P0001 | Hozelock | ? | Deluxe Hose Nozzle | Sale | 3 | 1 | 3 | 15.99 | 20.00% | 5.16 | 15.48 | ||
| 11 | 230246 | SHPR30K | Garden | ? | Hose & Spray Nozzle Set | Sale | 1 | 1 | 1 | 24.99 | 20.00% | 11.07 | 11.07 | ||
| 12 | 230217 | SHPR30 | Garden | ? | Reinforced Garden Hose | Sale | 1 | 1 | 1 | 29.99 | 20.00% | 10.12 | 10.12 | ||
| 13 | 739426 | 2515P0000 | Hozelock | ? | Vortex Multi Sprinkler | Sale | 1 | 1 | 1 | 25.29 | 20.00% | 9.62 | 9.62 | ||
| 14 | 239980 | 2292P9008 | Hozelock | ? | Nozzle Waterstop Unit | Sale | 1 | 10 | 10 | 15.99 | 20.00% | 46.25 | 46.25 | ||
| 15 | 267820 | 2292P9000 | Hozelock | ? | Hose Nozzle | Sale | 1 | 10 | 10 | 9.2 | 20.00% | 33.71 | 33.71 | ||
| 16 | 692714 | 2974P0000 | Hozelock | ? | Rectangular Sprinkler Plus | Sale | 2 | 1 | 2 | 39.99 | 20.00% | 15.77 | 31.54 | ||
Sheet1 | |||||||||||||||
