How to clear all "no fill" cells over 2000+ columns (~75 rows deep) VBA

[timeisanelephant77]

Hello PROS!
I have a file that had a description column for products that did not match up with the style categories of the stock#'s identified. I took the description column from the original file, copied it, and pasted it in a new doc, applied "text to column" to separate out all the keywords and then applied COPY/PASTE/TRANSLATE to make the rows of data into columns. The amount of rows was over 2000 ... which now has become 2000+ columns. I conditionally formatted the data to highlight Birthstones only and applied all the necessary rules. Now I have 2000+ columns with highlighted cells and "no-fill" cells (with data) that need to be sorted and/or segregated out. I do believe I need to achieve this in VBA, and I have tried ... but failed again and again thanks to my lack of experience and knowledge with VBA.

HOW do I either:
1. Delete/Clear Contents of ALL "NO FILL" cells in VBA across 2000+ columns
2. Move all "highlighted" cells to the top so I can manually delete everything else (in VBA)

I hope I can find help on this. Client gave us a terribly mixed up data file and I'm trying to learn if it is fixable ... or if I have to wait for a new file to clean up again.

THANK YOU!

See screenshot example: (here is just a small snapshot of the 2000+ columns I now have.):

 

[Yongle]

Try

Sub TidyUp()
Dim lastC As Long, lastR As Long, c As Long, r As Long, cel As Range, ws As Worksheet
Set ws = ActiveSheet
Set cel = Range(Split(ws.UsedRange.Address, ":")(1))
lastC = cel.Column: lastR = cel.Row
Application.ScreenUpdating = False: Application.CutCopyMode = False
    
For r = lastR To 2 Step -1
    For c = 1 To lastC
        Set cel = ws.Cells(r, c)
        If cel.Interior.ColorIndex = -4142 Then cel.Delete Shift:=xlUp
    Next c
Next r

End Sub

 

[timeisanelephant77]

Thank you. Giving it a try!