VBA need to run For loop multiple times to have desired outcome

Question

I am confused why this VBA code is behaving this strangely:

I have a sheet with a range of dates in the first row. Now I want to delete the whole column if the date is a saturday/sunday or a bank holiday:

holidays = Array("01.01.2022", "15.04.2022", "18.04.2022", "01.05.2022", "26.05.2022", "06.06.2022", "16.06.2022", "03.10.2022", "25.12.2022", "26.12.2022")
    Dim holiday As Variant

    For Each c In Range("B1", Range("B1").End(xlToRight)).Cells
        
        For Each holiday In holidays
            If c.Value = CDate(holiday) Then
                c.EntireColumn.Delete
            End If
        Next holiday
        
        If Weekday(c.Value, 2) > 5 Then
            c.EntireColumn.Delete
        End If
        
    Next c

If I run this macro it deletes all Saturdays only, if I run it a second time it deletes all Sundays too.

But why does it not delete all Saturdays and Sundays in the first run?

^^^^ "it" being the looping. try `For i = Range("B1").End(xlToRight) to 1 step -1` — cybernetic.nomad, Nov 08 '22 at 17:13
Think about it for a while: you found a Saturday in column B. Now when you delete column B, all columns get shifted to the left i.e. column C becomes now column B but the Loop doesn't know what happened it just continues with column C which is the former column D. So the new column B (containing the Sunday) never got checked. You can either loop backward with a For...Next loop or use the same For Each...Next loop but combine the found cells into a range and when the loop finishes, delete the range's entire columns in one go. — VBasic2008, Nov 08 '22 at 17:24

VBasic2008 · Accepted Answer · 2022-11-08T21:10:58.143

Delete Dates (`Union`)

This solution uses a For Each...Next loop combined with Union. It uses Application.Match instead of the 'holiday' loop. I got it to work only after the dates were converted to Longs.

Option Explicit

Sub DeleteDates()
    
    Dim Holidays() As Variant: Holidays = VBA.Array( _
        "01.01.2022", "15.04.2022", "18.04.2022", "01.05.2022", "26.05.2022", _
        "06.06.2022", "16.06.2022", "03.10.2022", "25.12.2022", "26.12.2022")
    
    Dim n As Long
    For n = 0 To UBound(Holidays)
        Holidays(n) = CLng(CDate(Holidays(n)))
    Next n
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    Dim rg As Range
    Set rg = ws.Range("B1", ws.Cells(1, ws.Columns.Count).End(xlToLeft))
    
    Dim drg As Range
    Dim Holiday As Variant
    Dim CurrDate As Date
    Dim cell As Range
    Dim DontDelete As Boolean
    
    For Each cell In rg.Cells
        ' Delete if not a date, or if Saturday, Sunday, or a holiday.
        If IsDate(cell.Value) Then
            CurrDate = cell.Value
            If Weekday(CurrDate, vbMonday) < 6 Then
                If IsError(Application.Match( _
                        CLng(CurrDate), Holidays, 0)) Then
                    DontDelete = True
                End If
            End If
        End If
        If DontDelete Then
            DontDelete = False
        Else
            If drg Is Nothing Then
                Set drg = cell
            Else
                Set drg = Union(drg, cell)
            End If
        End If
    Next cell
                    
    If drg Is Nothing Then Exit Sub
     
    drg.EntireColumn.Delete

End Sub

Thank you very much for your hint in the first comment and this solution here. I've just started today with VBA and learned a lot from your solution. — boost, Nov 08 '22 at 20:42

VBA need to run For loop multiple times to have desired outcome

1 Answers1

Delete Dates (Union)

Delete Dates (`Union`)