Keep only data that is highlighted

[Serafin54]

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.

• 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.

I am able to accomplish it via find/replace but just having a macro power tool at the ready to do all the steps needed would save time.

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

 

[Serafin54]

no. that is fine. that's a great addition to stop at the last line. Works perfectly. Thank you!

 

[Rick Rothstein]

Just noting that you can do what you want without using a loop...

Sub ClearUnhighlightedCells()
  With Application
    .ScreenUpdating = False
    .FindFormat.Clear
    .FindFormat.Interior.ColorIndex = 0
    ActiveSheet.UsedRange.Replace "", "", , , , , True, False
    .FindFormat.Clear
    .ScreenUpdating = True
  End With
End Sub

 

[gordsky]

Just noting that you can do what you want without using a loop...

Sub ClearUnhighlightedCells()
  With Application
    .ScreenUpdating = False
    .FindFormat.Clear
    .FindFormat.Interior.ColorIndex = 0
    ActiveSheet.UsedRange.Replace "", "", , , , , True, False
    .FindFormat.Clear
    .ScreenUpdating = True
  End With
End Sub

rick could you clarify your code. The user wanted a code that applied to a manual selection. Would yours not clear the entire 'Used Range' of the sheet rather than a user defined selection.

 

[Rick Rothstein]

rick could you clarify your code. The user wanted a code that applied to a manual selection.

Whoops! I missed that... thanks for catching it. The change is simple though, just use Selection in place of the ActiveSheet.UsedRange...

Sub ClearUnhighlightedCells()
  With Application
    .ScreenUpdating = False
    .FindFormat.Clear
    .FindFormat.Interior.ColorIndex = 0
    Selection.Replace "", "", , , , , True, False
    .FindFormat.Clear
    .ScreenUpdating = True
  End With
End Sub

 

[Serafin54]

Whoops! I missed that... thanks for catching it. The change is simple though, just use Selection in place of the ActiveSheet.UsedRange...

Sub ClearUnhighlightedCells()
  With Application
    .ScreenUpdating = False
    .FindFormat.Clear
    .FindFormat.Interior.ColorIndex = 0
    Selection.Replace "", "", , , , , True, False
    .FindFormat.Clear
    .ScreenUpdating = True
  End With
End Sub

Thanks Rick. I did try it but with the other code, the header row was omitted from being cleared out so that I would still be able to see the classification type. Other than that, it does work well

 

[Rick Rothstein]

I am not entirely clear on whether there is still an issue with my latest code or not. Did it work correctly for you are is there something (related to headers) that it still needs to address?

 

[Serafin54]

I am not entirely clear on whether there is still an issue with my latest code or not. Did it work correctly for you are is there something (related to headers) that it still needs to address?

Hi. Sorry. So what is needed of the code is that even though the user is selecting the entire column, the header doesn't get cleared out. Only the items beneath them.
In your code, if i select the entire column, since the header isn't highlighted, it gets cleared out which leaves no indicator (header). That would be the only update I can see.

 

[gordsky]

Hi. Sorry. So what is needed of the code is that even though the user is selecting the entire column, the header doesn't get cleared out. Only the items beneath them.
In your code, if i select the entire column, since the header isn't highlighted, it gets cleared out which leaves no indicator (header). That would be the only update I can see.

The code I supplied does exactly what you asked for. It leaves the header in place

 

[Serafin54]

The code I supplied does exactly what you asked for. It leaves the header in place

I am aware and I marked yours as the solution. However i can't ignore someone else's solution and feel only fair to give feedback. I have already used yours and it does what is needed. Thanks again.

 

[Tom.Jones]

Thanks Rick. I did try it but with the other code, the header row was omitted from being cleared out so that I would still be able to see the classification type. Other than that, it does work well

Hope, you don't select the headers.
For me Rick's VBA cod works very well.