Delete columns if all cells have a same criteria in entire cells

[uloveonce]

Hello,

I need to delete columns that have "'-" in the entire columns. If I filter that it will only shoe "-" in the drop down. I needed to delete all the columns that have those values in cells. I tried searching but could not come up with any.. There are multiple columns that have these values..

Please assist

Thanks.

 

[Trebor76]

Hi there,

Assuming the dashes are text not zeros formatted as dashes, try this while on the sheet in question (though initially on a copy of your data as the results cannot be undone if they're not as expected):

Option Explicit
Sub Macro1()

    Dim lngLastCol As Long
    Dim lngMyCol   As Long
    Dim strMyCol   As String
    
    lngLastCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    Application.ScreenUpdating = False
    
    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)
        If Evaluate("CountIf(" & strMyCol & ":" & strMyCol & ",""-"")") >= 1 Then
            Columns(strMyCol).EntireColumn.Delete
        End If
    Next lngMyCol
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[uloveonce]

Thanks Trebor.

Worked perfectly on test.. Will try on main file and let you know..