Hi,
Is there a way via VBA to select a range of columns in a worksheet and then clear out any cell that isn't highlighted so I'm not going column by column to filter out cells with no fill and doing it manually? The columns that are needed to be cleared out may differ time and again so i can't say that the range will always be (B:M).
I was trying a couple codes i found online and tweak them but I'm heading down a rabbit hole quickly and record macro is really bulky and not forgiving.
Thanks!!!!!
before:
After:
Is there a way via VBA to select a range of columns in a worksheet and then clear out any cell that isn't highlighted so I'm not going column by column to filter out cells with no fill and doing it manually? The columns that are needed to be cleared out may differ time and again so i can't say that the range will always be (B:M).
I was trying a couple codes i found online and tweak them but I'm heading down a rabbit hole quickly and record macro is really bulky and not forgiving.
- On this example for example, I want to be able to select the columns i want to run the code or the ones not to include. either or but thinking selecting which to include is easiest for coding. it will not always just be omitting column A. It could be A-D being omitted or etc.
- Have the code ignore the header row
- Clear the cells of any that are not highlighted in any way.
Thanks!!!!!
before:
Book1 | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
1 | Date | East Color | East Units | East Sales | North Color | North Units | North Sales | South Color | South Units | South Sales | West Color | West Units | West Sales | ||
2 | 01/03/2016 | x | x | x | x | x | x | x | x | x | 1 | 1 | 11 | ||
3 | 01/13/2016 | x | x | x | x | x | x | 1 | 8 | 96 | x | x | x | ||
4 | 01/21/2016 | x | x | x | x | x | x | x | x | x | 1 | 2 | 26 | ||
5 | 01/30/2016 | x | x | x | 1 | 7 | 84 | x | x | x | x | x | x | ||
6 | 02/07/2016 | x | x | x | 1 | 8 | 104 | x | x | x | x | x | x | ||
7 | 02/13/2016 | x | x | x | x | x | x | 1 | 2 | 22 | x | x | x | ||
8 | 02/21/2016 | 1 | 5 | 60 | x | x | x | x | x | x | x | x | x | ||
9 | 03/01/2016 | x | x | x | x | x | x | x | x | x | 1 | 2 | 26 | ||
10 | 03/13/2016 | 1 | 8 | 96 | x | x | x | x | x | x | x | x | x | ||
11 | 03/23/2016 | x | x | x | 1 | 7 | 84 | x | x | x | x | x | x | ||
12 | 03/28/2016 | x | x | x | x | x | x | x | x | x | 1 | 2 | 26 | ||
13 | 04/03/2016 | x | x | x | x | x | x | 1 | 8 | 96 | x | x | x | ||
14 | 04/12/2016 | x | x | x | x | x | x | 1 | 1 | 13 | x | x | x | ||
15 | 04/16/2016 | 1 | 8 | 88 | x | x | x | x | x | x | x | x | x | ||
16 | 04/23/2016 | x | x | x | x | x | x | x | x | x | 1 | 6 | 66 | ||
17 | 04/30/2016 | x | x | x | x | x | x | 1 | 5 | 65 | x | x | x | ||
18 | 05/09/2016 | x | x | x | x | x | x | 1 | 7 | 84 | x | x | x | ||
19 | 05/16/2016 | x | x | x | x | x | x | 1 | 5 | 55 | x | x | x | ||
20 | 05/25/2016 | x | x | x | x | x | x | 1 | 1 | 12 | x | x | x | ||
21 | 05/30/2016 | x | x | x | 1 | 4 | 48 | x | x | x | x | x | x | ||
22 | 06/04/2016 | x | x | x | 1 | 7 | 84 | x | x | x | x | x | x | ||
23 | 06/13/2016 | 1 | 3 | 33 | x | x | x | x | x | x | x | x | x | ||
24 | 06/21/2016 | x | x | x | x | x | x | 1 | 2 | 24 | x | x | x | ||
25 | 06/26/2016 | x | x | x | x | x | x | 1 | 6 | 72 | x | x | x | ||
26 | 07/02/2016 | 1 | 6 | 66 | x | x | x | x | x | x | x | x | x | ||
27 | 07/08/2016 | x | x | x | x | x | x | x | x | x | 1 | 2 | 26 | ||
28 | 07/12/2016 | x | x | x | x | x | x | 1 | 4 | 48 | x | x | x | ||
29 | 07/19/2016 | x | x | x | x | x | x | 1 | 1 | 13 | x | x | x | ||
30 | 07/26/2016 | x | x | x | x | x | x | x | x | x | 1 | 1 | 12 | ||
31 | 07/31/2016 | x | x | x | x | x | x | 1 | 2 | 22 | x | x | x | ||
32 | 08/07/2016 | x | x | x | x | x | x | x | x | x | 1 | 5 | 65 | ||
33 | 08/13/2016 | x | x | x | x | x | x | x | x | x | 1 | 5 | 60 | ||
34 | 08/20/2016 | x | x | x | 1 | 8 | 104 | x | x | x | x | x | x | ||
35 | 08/25/2016 | x | x | x | x | x | x | x | x | x | 1 | 4 | 48 | ||
36 | 09/01/2016 | x | x | x | x | x | x | 1 | 6 | 66 | x | x | x | ||
37 | 09/07/2016 | x | x | x | x | x | x | 1 | 7 | 91 | x | x | x | ||
38 | 09/10/2016 | x | x | x | 1 | 4 | 44 | x | x | x | x | x | x | ||
Original |
After:
Book1 | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
1 | Date | East Color | East Units | East Sales | North Color | North Units | North Sales | South Color | South Units | South Sales | West Color | West Units | West Sales | ||
2 | 01/03/2016 | 1 | 1 | 11 | |||||||||||
3 | 01/13/2016 | 1 | |||||||||||||
4 | 01/21/2016 | 1 | |||||||||||||
5 | 01/30/2016 | 1 | |||||||||||||
6 | 02/07/2016 | 1 | 104 | ||||||||||||
7 | 02/13/2016 | 1 | |||||||||||||
8 | 02/21/2016 | 1 | |||||||||||||
9 | 03/01/2016 | 1 | |||||||||||||
10 | 03/13/2016 | 1 | |||||||||||||
11 | 03/23/2016 | 1 | |||||||||||||
12 | 03/28/2016 | 1 | |||||||||||||
13 | 04/03/2016 | 1 | |||||||||||||
14 | 04/12/2016 | 1 | 1 | 13 | |||||||||||
15 | 04/16/2016 | 1 | |||||||||||||
16 | 04/23/2016 | 1 | |||||||||||||
17 | 04/30/2016 | 1 | |||||||||||||
18 | 05/09/2016 | 1 | |||||||||||||
19 | 05/16/2016 | 1 | |||||||||||||
20 | 05/25/2016 | 1 | 1 | 12 | |||||||||||
21 | 05/30/2016 | 1 | |||||||||||||
22 | 06/04/2016 | 1 | |||||||||||||
23 | 06/13/2016 | 1 | |||||||||||||
24 | 06/21/2016 | 1 | |||||||||||||
25 | 06/26/2016 | 1 | |||||||||||||
26 | 07/02/2016 | 1 | |||||||||||||
27 | 07/08/2016 | 1 | |||||||||||||
28 | 07/12/2016 | 1 | |||||||||||||
29 | 07/19/2016 | 1 | 1 | 13 | |||||||||||
30 | 07/26/2016 | 1 | 1 | 12 | |||||||||||
31 | 07/31/2016 | 1 | |||||||||||||
32 | 08/07/2016 | 1 | |||||||||||||
33 | 08/13/2016 | 1 | |||||||||||||
34 | 08/20/2016 | 1 | 104 | ||||||||||||
35 | 08/25/2016 | 1 | |||||||||||||
36 | 09/01/2016 | 1 | |||||||||||||
37 | 09/07/2016 | 1 | 91 | ||||||||||||
38 | 09/10/2016 | 1 | |||||||||||||
Results |