Search code examples
excelvba

Keep Columns on Data set based on column header and hide all others

I am working with a big data set, but every time we download a new file the columns may vary. I need to keep only 25 columns all the time and hide all other columns.

Data Headers

I created a code to hide the columns I didn't need, but I realize it is not going to work all the time. Could someone help me?

for example, I only need columns "Material" and "material type"

I tried hiding columns by index but I need to keep only some specific headers and hide all others

Also, I tried this code but didn't work.

Sub Hide_Rows_Based_On_Header_and_Value()

    Dim a As Long
    Dim w As Long
    Dim vKeepCOLs As Variant
    Dim vCOLNDX As Variant
    Dim r As Long
    

    vKeepCOLs = Array("Material", "Plant", "Batch Management", "Plant-Sp.Matl Status", "Unit of issue", _
    , "MRP Type", "Planned Deliv. Time", "GR processing time", "Procurement Type", "Minimum Lot Size" _
    , "Maximum Lot Size", "Rounding value", "Backflush", "Overdely tolerance", "Underdely tolerance" _
    , "Batch Management(Plant)", "Consumption mode", "Bwd consumption per.", "Fwd consumption per." _
    , "Production unit", "Prod. stor. location", "Neg. stocks in plant", "Planning Strategy Group" _
    , "Storage loc. for EP", "Batch entry")
    
    With Worksheets("MARC")
        For w = 1 To .Worksheets.Count
            With Worksheets(w)
                For a = LBound(vKeepCOLs) To UBound(vKeepCOLs)
                    vCOLNDX = Application.Match(vKeepCOLs(a), .Rows(1), 0)
                    If Not IsError(vCOLNDX) Then                   
                            .EntireColumn.Hidden                                
                           Exit For                   
                    End If
                Next a
            End With
        Next w
    End With
End Sub
Solution

    • If there are numerous columns on the sheet and the visible columns are relatively few, hiding all columns and then unhiding the necessary ones would be more efficient.

    • Hidden is a property of Range object. Set the value to True to hidden the column/row.

    Microsoft documentation:

    Range.Hidden property (Excel)

    Sub Demo()
    Dim aKeepCol As Variant, vCol As Variant, iCol As Variant
    aKeepCol = Array("Material", "material type")
    With ActiveSheet
        .UsedRange.EntireColumn.Hidden = True
        For Each vCol In aKeepCol
            iCol = Application.Match(vCol, .Rows(1), 0)
            If Not IsError(iCol) Then .Columns(iCol).Hidden = False
        Next
    End With
End Sub
    • If there are multiple sheets in the workbook
    Sub Demo()
    Dim aKeepCol As Variant, vCol As Variant, iCol As Variant
    Dim oSht As Worksheet
    aKeepCol = Array("Material", "material type")
    For Each oSht In ActiveWorkbook.Worksheets
        With oSht
            .UsedRange.EntireColumn.Hidden = True
            For Each vCol In aKeepCol
                iCol = Application.Match(vCol, .Rows(1), 0)
                If Not IsError(iCol) Then .Columns(iCol).Hidden = False
            Next
        End With
    Next
End Sub