Removing certain columns based on a list of column headers

[boim]

Hi,

I have a table with several un-needed columns that I'd like to remove. In the VBA code I defined a list (an array) that contains the column headers that will be removed. I devised a solution using ListColumn object (SOLUTION #2), and while this solution works fine, I tried a different way of doing it with HeaderRowRange (SOLUTION #1). The problem is that this solution (SOLUTION #1) doesn't work and I don't understand why. Could someone please explain?

Note: SOLUTION #1 doesn't work because it skips certain columns (Real time, Must C/Out, WeekEnd_OT). You can checked the skipped columns by looking at result of Debug.Print in the Immediate Window or just by commenting out one of the SOLUTIONs.

Thanks

Option Explicit
Sub InitializeTimesheetColumns()
'
    Const colDate = "Date"
    Const colTimetable = "Timetable"
    Const colOnDuty = "On duty"
    Const colOffDuty = "Off duty"
    Const colClockIn = "Clock In"
    Const colClockOut = "Clock Out"
    Const colWorkTime = "Work Time"
    Const colATTTime = "ATT_Time"
    
    Dim arrRemovedColumns
    Dim strRemovedColumn As Variant

    Dim intTotalRow As Integer
    
    Dim tb As ListObject
    Dim lc As ListColumn
    Dim lc_header As Range
    Dim c As Range
    
    arrRemovedColumns = Array("Normal", "Real time", "OT Time", "Must C/In", "Must C/Out", "NDays", _
        "WeekEnd", "Holiday", "NDays_OT", "WeekEnd_OT", "Holiday_OT")
   
    With Sheet1
       
        ' Hitung jumlah baris data absensi
        intTotalRow = Application.WorksheetFunction.CountA(.Columns("A"))
        Debug.Print intTotalRow

        ' Remove un-needed columns
        Set tb = .ListObjects("Table1")
        Debug.Print tb.Name
        Set lc_header = tb.HeaderRowRange
        Debug.Print lc_header.Address
        
        ' SOLUTION #1 - skips certain columns
        For Each c In lc_header
            Debug.Print "ColName:" & " " & c.Value
            For Each strRemovedColumn In arrRemovedColumns
                If c.Value = strRemovedColumn Then
                    Debug.Print "REM:" & " " & strRemovedColumn & " " & tb.ListColumns(strRemovedColumn).Name
                    tb.ListColumns(strRemovedColumn).Delete
                    Exit For
                End If
            Next
        Next
        
        ' SOLUTION #2 - works OK
        For Each lc In tb.ListColumns
            Debug.Print "ColName:" & " " & lc.Name
            For Each strRemovedColumn In arrRemovedColumns
                If lc.Name = strRemovedColumn Then
                    Debug.Print "REM:" & " " & strRemovedColumn & " " & tb.ListColumns(strRemovedColumn).Name
                    lc.Delete
                    Exit For
                End If
            Next
        Next
        
    End With
    
End Sub

 

[Andrew Poulsom]

Welcome to the Board.

To delete columns you need to loop backwards, otherwise one may be skipped.

 

[boim]

I think I understand what you mean. In SOLUTION #1, the removed columns are in a changing range. Everytime a column is removed, the headers shifted to the left, So because the loop actually runs on the range address, if the code loops forward, it may miss a supposedly removed column whose address is behind the counter due to the shift, right?

I just switch the order of the loop and now it works.

Thanks.