VBA需要多次运行For循环才能得到期望的结果

Question

我很困惑为什么这个VBA代码的行为如此奇怪:

我有一个在第一行有一系列日期的工作表。现在，如果日期是星期六/星期日或银行假日，我想删除整个列:

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

如果我运行这个宏，它只删除所有的星期六，如果我第二次运行它，它也删除所有的星期天。

但是为什么它没有在第一次运行时删除所有的星期六和星期日呢?

Answer 1

删除日期(Union)

• 本方案采用For Each...Next环与Union环相结合。它使用Application.Match代替'holiday'循环。只有在日期转换为长后，它才能工作。

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