Excel VBA：删除旧记录 [英] Excel VBA: Delete Old Records

问题描述

我试图删除任何180天或以上的记录。日期在F栏。当我运行时，没有任何反应。我认为它与Date（）函数有关。

I'm attempting to delete any records that are 180 days or older. The dates are in Column F. When I run this, nothing happens. I'm thinking that it has to do with the Date() function.

Sub ClearOldData()
Application.ScreenUpdating = False
Sheets("Data").Select
Dim LastRow As Long
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For i = 2 To LastRow
    Dim recdate As Date
    recdate = Cells(i, "F").Value 
    If DateDiff(d, Date, recdate) > 179 Then
        ws.Rows(i).Delete
    End If
Next i
Application.ScreenUpdating = True
End Sub

推荐答案

在VBA中，日期/时间是从1开始的数值，等于＃1/1 / 1900＃。

In the VBA Date/Time is a numeric value starting at 1 which equates to #1/1/1900#.

日期/时间值

• 1天= 1


• 1小时= 1/24


• 1分钟= 1/24/60


• 1第二= 1/24/60/60

• 1 Day = 1
• 1 Hour = 1/24
• 1 Minute = 1/24/60
• 1 Second = 1/24/60/60

一直从最后一行/项目删除到第一个。我对比较的答案单元格删除行，值为true但不删除行演示为什么。

As always delete from the last row/item to the first. My answer to Compare cells to delete rows, value is true but not deleting rows demonstrates why.

DateDiff（d，Date，recdate）> 179 和 Int（Date - recdate）> 179 相当。

Sub ClearOldData()
    Application.ScreenUpdating = False
    Dimi As Long

    With Sheets("Data")

        For i = 2 To LastRow Step -1
            If Int(Date - Cells(i, "F").Value) > 179 Then
                .Rows(i).Delete
            End If
        Next i

    End With
    Application.ScreenUpdating = True
End Sub

这篇关于Excel VBA：删除旧记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！