VBA：在纸张上双重过滤，如何在两个确定的间隔之间过滤掉数据 [英] VBA: Double filter on a sheet, how to filter out data between two certain intervals

问题描述

我需要 VBA 代码从特定时间窗口过滤掉警报日志。某些物体上出现并清除警报。我需要脚本过滤掉两个指定时间之间的闹钟（例如，在1AM之后/之后出现的闹钟，并在3AM之前/之前清除），并将过滤后的数据复制到另一个工作表。请参阅附图。

I need the VBA code to filter out the alarm logs from a certain time window. Alarms appear and clear out on some objects. I need the script the filter out the alarms between two specified times (e.g those which appeared on/after 1AM AND were cleared by/before 3AM), and copy the filtered data to another sheet. Please refer to the attached image.

查看给出的图像，显​​示所有的闹钟日志。

See the given image, which shows the all the alarm logs.

推荐答案

我不得不修改你的数据，清除时间符合您的条件。

I had to modify your data somewhat as no raised and cleared time met your criteria.

Option Explicit

Sub betweenTimes()
    With Worksheets("Sheet5")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Range(.Cells(2, "A"), .Cells(.Rows.Count, "G").End(xlUp))
            'as hardcoded values
            '.AutoFilter field:=2, Criteria1:=">=" & Format(Date + TimeSerial(1, 0, 0), "yyyy-mm-dd hh:mm:ss")
            '.AutoFilter field:=3, Criteria1:="<=" & Format(Date + TimeSerial(3, 0, 0), "yyyy-mm-dd hh:mm:ss")
            'as values from I3:J3
            .AutoFilter field:=2, Criteria1:=Format(.Parent.Cells(3, "I").Value2, "\>\=yyyy-mm-dd hh:mm:ss")
            .AutoFilter field:=3, Criteria1:=Format(.Parent.Cells(3, "J").Value2, "\<\=yyyy-mm-dd hh:mm:ss")
            With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
                If CBool(Application.Subtotal(103, .Cells)) Then
                    Union(.Cells, .Cells(1, "XFD")).Copy Destination:=Worksheets("Sheet6").Cells(2, "A")
                End If
            End With
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With
End Sub

这篇关于VBA：在纸张上双重过滤，如何在两个确定的间隔之间过滤掉数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！