Keep only data that is highlighted

Serafin54

Board Regular
Joined
Apr 11, 2014
Messages
155
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLM
1DateEast ColorEast UnitsEast SalesNorth ColorNorth UnitsNorth SalesSouth ColorSouth UnitsSouth SalesWest ColorWest UnitsWest Sales
201/03/2016xxxxxxxxx1111
301/13/2016xxxxxx1896xxx
401/21/2016xxxxxxxxx1226
501/30/2016xxx1784xxxxxx
602/07/2016xxx18104xxxxxx
702/13/2016xxxxxx1222xxx
802/21/20161560xxxxxxxxx
903/01/2016xxxxxxxxx1226
1003/13/20161896xxxxxxxxx
1103/23/2016xxx1784xxxxxx
1203/28/2016xxxxxxxxx1226
1304/03/2016xxxxxx1896xxx
1404/12/2016xxxxxx1113xxx
1504/16/20161888xxxxxxxxx
1604/23/2016xxxxxxxxx1666
1704/30/2016xxxxxx1565xxx
1805/09/2016xxxxxx1784xxx
1905/16/2016xxxxxx1555xxx
2005/25/2016xxxxxx1112xxx
2105/30/2016xxx1448xxxxxx
2206/04/2016xxx1784xxxxxx
2306/13/20161333xxxxxxxxx
2406/21/2016xxxxxx1224xxx
2506/26/2016xxxxxx1672xxx
2607/02/20161666xxxxxxxxx
2707/08/2016xxxxxxxxx1226
2807/12/2016xxxxxx1448xxx
2907/19/2016xxxxxx1113xxx
3007/26/2016xxxxxxxxx1112
3107/31/2016xxxxxx1222xxx
3208/07/2016xxxxxxxxx1565
3308/13/2016xxxxxxxxx1560
3408/20/2016xxx18104xxxxxx
3508/25/2016xxxxxxxxx1448
3609/01/2016xxxxxx1666xxx
3709/07/2016xxxxxx1791xxx
3809/10/2016xxx1444xxxxxx
Original


After:

Book1
ABCDEFGHIJKLM
1DateEast ColorEast UnitsEast SalesNorth ColorNorth UnitsNorth SalesSouth ColorSouth UnitsSouth SalesWest ColorWest UnitsWest Sales
201/03/20161111
301/13/20161
401/21/20161
501/30/20161
602/07/20161104
702/13/20161
802/21/20161
903/01/20161
1003/13/20161
1103/23/20161
1203/28/20161
1304/03/20161
1404/12/20161113
1504/16/20161
1604/23/20161
1704/30/20161
1805/09/20161
1905/16/20161
2005/25/20161112
2105/30/20161
2206/04/20161
2306/13/20161
2406/21/20161
2506/26/20161
2607/02/20161
2707/08/20161
2807/12/20161
2907/19/20161113
3007/26/20161112
3107/31/20161
3208/07/20161
3308/13/20161
3408/20/20161104
3508/25/20161
3609/01/20161
3709/07/2016191
3809/10/20161
Results
 
Here is my code modified to skip Row 1 if Row 1 is included in the selection, otherwise it uses the selection as is...
VBA Code:
Sub ClearUnhighlightedCells()
  With Application
    .ScreenUpdating = False
    .FindFormat.Clear
    .FindFormat.Interior.ColorIndex = 0
    Selection(1).Offset(-(Selection.Row = 1)).Resize(Selection.Rows.Count + (Selection.Row = 1), Selection.Columns.Count).Replace "", "", , , , , True, False
    .FindFormat.Clear
    .ScreenUpdating = True
  End With
End Sub
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Here is my code modified to skip Row 1 if Row 1 is included in the selection, otherwise it uses the selection as is...
VBA Code:
Sub ClearUnhighlightedCells()
  With Application
    .ScreenUpdating = False
    .FindFormat.Clear
    .FindFormat.Interior.ColorIndex = 0
    Selection(1).Offset(-(Selection.Row = 1)).Resize(Selection.Rows.Count + (Selection.Row = 1), Selection.Columns.Count).Replace "", "", , , , , True, False
    .FindFormat.Clear
    .ScreenUpdating = True
  End With
End Sub

I had a chance to have a coworker try this code today on a file and they told me it was just about instant and did exactly what was needed. Thanks Rick!
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

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
Back
Top