Help with deleting columns based on conditional formatting please

[karenhn]

I have a spreadsheet with 72 rows and about 10,000 columns. I have applied conditional formatting to highlight all the cells which have a value of less than 150. Is there a way of now deleting all the columns where ALL the cells are highlighted/less than 150? At the moment I have the cells filled in red to make it easier to see the columns with white cells that I want to keep, but I don't want to manually go through all the columns!

Thanks.

 

[Trebor76]

Hi karenhn,

Welcome to MrExcel!!

Try this (initially on a copy of your data as the results cannot be undone if they're not as expected) which doesn't use conditional formatting but simply checks the maximum value in each column before deleting it if this value is less then 150:

Option Explicit
Sub Macro1()

    'http://www.mrexcel.com/forum/excel-questions/978710-help-deleting-columns-based-conditional-formatting-please.html

    Dim lngLastCol As Long
    Dim lngMyCol   As Long
    Dim lngLastRow As Long
    Dim strMyCol   As String
    
    Application.ScreenUpdating = False

    lngLastCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    For lngMyCol = lngLastCol To 1 Step -1
        strMyCol = Left(Cells(1, lngMyCol).Address(True, False), Application.WorksheetFunction.Search("$", Cells(1, lngMyCol).Address(True, False)) - 1)
        lngLastRow = Cells(Rows.Count, strMyCol).End(xlUp).Row
        If Evaluate("MAX(" & strMyCol & "1:" & strMyCol & lngLastRow & ")") < 150 Then
            Columns(strMyCol).EntireColumn.Delete
        End If
    Next lngMyCol
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[karenhn]

That worked perfectly! Thank you so much!

 

[Trebor76]

Thanks for letting us know and you're welcome