Forum Discussion
Deleting entire columns if background colour is red
HansVogelaar
Thank you very much for this. I have never tried a macro before.
I think I need to open this in VBA? So alt f11 to open VBA, insert module, add this macro and alt f5 to run it?
Have I missed anything? And what do I need to do after these steps if they are right?
Apologies as new to all of this and trying to learn as I go!
update** I have run this, it has taken away the red columns but also the none red columns, everything has been deleted.
Robert1290 Did every column have at least one cell with a red background?
HansVogelaarNo it didn't, a lot of columns are white with black text.
This may make a difference, the reason the columns are red are due to conditional formatting making them go red.
Yes, that does make a difference. Here is a new version.
Sub DeleteRed() Dim rng As Range Dim cel As Range Dim trg As Range Application.ScreenUpdating = False On Error Resume Next Set rng = Cells.SpecialCells(xlCellTypeAllFormatConditions) On Error GoTo 0 For Each cel In rng If cel.DisplayFormat.Interior.Color = vbRed Then If trg Is Nothing Then Set trg = cel Else Set trg = Union(trg, cel) End If End If Next cel If Not trg Is Nothing Then trg.EntireColumn.Delete End If Application.ScreenUpdating = True End Sub- @ Hans Vogelaar Thank you.
When I run that macro I get an error: runtime error 1004 cannot use that command on overlapping selections
It then highlights in yellow this part of the macro:
trg.EntireColumn.Delete