excel 获取两个时间之间的时间间隔的vba代码

4szc88ey 于 2022-11-18 发布在其他

关注(0)|答案(1)|浏览(143)

需要过滤的原始数据需要帮助过滤两个时间之间的值，例如开始时间将是00：00到14：00，我需要过滤这些值之间的值，复制相应的数据并粘贴到另一个工作表中

Option Explicit

Sub Filter_My_Data()

Dim Data_sh As Worksheet
Dim Filter_Criteria_Sh As Worksheet
Dim Output_sh As Worksheet

Set Data_sh = ThisWorkbook.Sheets("Data")
Set Filter_Criteria_Sh = ThisWorkbook.Sheets("Filter_Criteria")
Set Output_sh = ThisWorkbook.Sheets("Output")

Output_sh.UsedRange.Clear
Data_sh.AutoFilterMode = False

Dim timelist() As long
Dim n As Integer

n = Application.WorksheetFunction.CountA(Filter_Criteria_Sh.Range("A:A")) - 2

ReDim timelist(n) As long
Dim i As Integer

For i = 0 To n
    timelist(i) = Filter_Criteria_Sh.Range("A" & i + 2)
Next i

Data_sh.UsedRange.AutoFilter 3, timelist(), xlFilterValues
Data_sh.UsedRange.Copy Output_sh.Range("A1")
Data_sh.AutoFilterMode = False

MsgBox ("Data has been Copied")

End Sub

excel

来源：https://stackoverflow.com/questions/74379813/vba-code-to-get-a-time-interval-between-two-times

1条答案

按热度按时间

htrmnn0y1#

按时间间隔筛选

Sub FilterByTimeInterval()
    
    Const StartString As String = "00:00:00"
    Const EndString As String = "14:00:00"
    
    Dim StartCrit As String: StartCrit = ">=" & CDate(StartString)
    Dim EndCrit As String: EndCrit = "<=" & CDate(EndString)
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    Dim sws As Worksheet: Set sws = wb.Worksheets("Data")
    If sws.FilterMode Then sws.ShowAllData
    Dim srg As Range: Set srg = sws.UsedRange
    
    srg.AutoFilter 3, StartCrit, xlAnd, EndCrit
    
    Dim dws As Worksheet: Set dws = wb.Worksheets("Output")
    dws.UsedRange.Clear
    Dim dfCell As Range: Set dfCell = dws.Range("A1")
    
    srg.Copy dfCell
    
    sws.AutoFilterMode = False

End Sub

赞(0）回复(0）举报 2022-11-18

我来回答

excel 获取两个时间之间的时间间隔的vba代码

1条答案

按时间间隔筛选

相关问题

热门标签

最新问答