我发现了另一个链,导致我根据它们包含的静态文本删除单元格,但现在我还需要以类似的方式删除日期,但它们是可变的。
这是我用于静态文本删除的代码:
'Sub DeleteRowBasedOnCriteria()
Dim RowToTest As Long
For RowToTest = Cells(Rows.count, 6).End(xlUp).Row To 2 Step -1
With Cells(RowToTest, 6)
If .value <> "ALLOW" _
And .value <> "CHRG" _
And .value <> "COST" _
Then _
Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest
End Sub
这工作得很好,但是当我运行这个报告时,我运行它前2或3天(取决于星期几)。我需要删除在最早运行日期前两天的任何内容。例如:如果我在24日运行22日和23日的报告,我想删除日期早于20日的任何内容。
我知道报告似乎可以运行来做这个,但相信我,它就像霸王龙做后空翻一样无能为力。我试图改变静态文本的代码,但由于对VBA代码的了解有限,我不知道我是否在正确的轨道上:
'Sub DeleteRowBasedOnCriteria()
Dim RowToTest As Long
For RowToTest = Cells(Rows.count, 5).End(x1Up).Row To 2 Step -1
With Cells(RowToTest, 5)
If .value < Today _
Then _
Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest
End Sub
显然,我没有任何东西可以解释今天的日期减去报告运行前4天的值,所以我也需要帮助。在当前的形式中,我得到了"应用程序定义或对象定义的错误":
For RowToTest = Cells(Rows.count, 5).End(x1Up).Row To 2 Step -1
提前感谢您的帮助和建议。
编辑:InputBox添加
使用日期而不是今天。当Rows(). delete .
时,不需要EntireRow。子M1 ()"子DeleteRowBasedOnCriteria ()Dim RowToTest As LongDim DayCount As Long
DayCount = Application.InputBox(Prompt:="How Days Back to CleanUp?", Default:=3, Type:=1)
For RowToTest = Cells(Rows.Count, 6).End(xlUp).Row To 2 Step -1
With Cells(RowToTest, 6)
If .Value <> "ALLOW" _
And .Value <> "CHRG" _
And .Value <> "COST" _
Then _
Rows(RowToTest).Delete
End With
Next RowToTest
For RowToTest = Cells(Rows.Count, 5).End(xlUp).Row To 2 Step -1
With Cells(RowToTest, 5)
If .Value < (Date - DayCount) Then Rows(RowToTest).Delete
End With
Next RowToTest
结束子