excel - AutoFilter返回正确的结果，但是当 SpecialCells(xlCellTypeVisible).value 时，excel只返回一半的记录

Question

这是我的代码

openWs.AutoFilterMode = False
Range("A1").AutoFilter Field:=1, Criteria1:= _
">=" & date1, Operator:=xlAnd, Criteria2:="<=" & date2
Range("A1").AutoFilter Field:=4, VisibleDropDown:=False
Range("A1").AutoFilter Field:=5, VisibleDropDown:=False
Range("A1").AutoFilter Field:=6, VisibleDropDown:=False
Range("A1").AutoFilter Field:=7, VisibleDropDown:=False
Range("A1").AutoFilter Field:=8, VisibleDropDown:=False
cnt = ActiveSheet.UsedRange.Rows.count
arr() = openWs.Range("A2:H" & cnt).Rows.SpecialCells(xlCellTypeVisible).Value 'Supplier

自动过滤器显示我有 40 条记录，但是当使用 .SpecialCells(xlCellTypeVisible) 时，我只从中得到大约 20 条记录，在检查记录后，我发现它总是返回与date2，范围后面的数据会被忽略，为什么？

//过滤后的样本数据

过滤范围20130101 - 20130107

20130104    7339/01/13  13006015    CN  -250000 -639442350.6  //Success
20130107    7346/01/13  13002022    DN  1200000 -639442350.6 //Success
//data below all ignore after hitting 20130107, only return data above target
20130104    7340/01/13  13006016    CN  -300000 118968559.6 //Failed
20130107    7340/01/13  13006016    CN  -300000 118968559.6 //Failed

我做错了什么吗？我以为excel会自动返回过滤器后可见的所有内容，但似乎它仍然应用相同的过滤器？有什么想法吗？

更新 1更多示例数据，应用 Siddharth Rout 代码后

CP0001  20130103    I/TAX - 12TH INST YA2012    13012000    PR  -180000 0
CP0001  20130103    I/TAX - 12TH INST YA2012    13014000    PPR 180000  0
HH2000  20130102    7324/01/13  13006000    CN  -100000 -639442350.6
HH2000  20130102    7325/01/13  13002001    DN  1500000 -639442350.6
HH2000  20130103    7326/01/13  13006002    CN  -17000000   -639442350.6
HH2000  20130103    7348/01/13  13006024    CN  -3000000    -639442350.6
HH2000  20130104    7327/01/13  13006003    CN  -10000000   -639442350.6
HH2000  20130104    7328/01/13  13006004    CN  -10000000   -639442350.6
HH2000  20130104    7329/01/13  13006005    CN  -500000 -639442350.6
HH2000  20130104    7330/01/13  13006006    CN  -1500000    -639442350.6
HH2000  20130104    7331/01/13  13006007    CN  -1000000    -639442350.6
HH2000  20130104    7332/01/13  13006008    CN  -3000000    -639442350.6
HH2000  20130104    7333/01/13  13006009    CN  -135000 -639442350.6
HH2000  20130104    7334/01/13  13006010    CN  -5000000    -639442350.6
HH2000  20130104    7335/01/13  13006011    CN  -31099000   -639442350.6
HH2000  20130104    7336/01/13  13006012    CN  -7000   -639442350.6
HH2000  20130104    7337/01/13  13006013    CN  -5000   -639442350.6
HH2000  20130104    7338/01/13  13006014    CN  -700000 -639442350.6
HH2000  20130104    7339/01/13  13006015    CN  -250000 -639442350.6
HH2000  20130107    7341/01/13  13006017    CN  -4563000    -639442350.6
HH2000  20130107    7343/01/13  13006019    CN  -1800000    -639442350.6
HH2000  20130107    7344/01/13  13002020    DN  800000  -639442350.6
HH2000  20130107    7345/01/13  13002021    DN  900000  -639442350.6
HH2000  20130107    7346/01/13  13002022    DN  1200000 -639442350.6 //it stop here, below all ignore by excel
HH2030  20130104    7338/01/13  13002014    DN  700000  5318670.54
HH2100  20130104    7340/01/13  13006016    CN  -300000 118968559.6
HH2100  20130107    7342/01/13  13006018    CN  -980000 118968559.6
HH2101  20130107    7347/01/13  13006023    CN  -300000 -12587577.27

Answer 1

根据我的评论，尝试一下这个。这对我有用。

编辑:为了避免混淆，@mehow 下面提到的内容是绝对正确的。以下只是示例数据。当您实际处理必须应用自动筛选的数据时，请使用第一行中的标题，然后使用 .Offset(1, 0).SpecialCells(xlCellTypeVisible) 获取筛选后的数据，如图 HERE

代码:

Sub Sample()
    Dim openWs As Worksheet
    Dim rng As Range, VisbRange As Range
    Dim lRow As Long, date1  as Long, date2 as Long
    Set openWs = ThisWorkbook.Sheets("Sheet1")

    date1 = 20130101
    date2 = 20130107

    With openWs
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            lRow = 1
        End If

        Set rng = .Range("A1:H" & lRow)

        .AutoFilterMode = False

        With rng

            .AutoFilter Field:=1, Criteria1:= _
            ">=" & date1, Operator:=xlAnd, Criteria2:="<=" & date2

            .AutoFilter Field:=4, VisibleDropDown:=False
            .AutoFilter Field:=5, VisibleDropDown:=False
            .AutoFilter Field:=6, VisibleDropDown:=False
            .AutoFilter Field:=7, VisibleDropDown:=False
            .AutoFilter Field:=8, VisibleDropDown:=False

            Set VisbRange = .Rows.SpecialCells(xlCellTypeVisible)
            Debug.Print VisbRange.Address
        End With

        .AutoFilterMode = False
    End With
End Sub

屏幕截图:(之前之后)

后续表单评论:

这就是你正在尝试的吗？

Sub Sample()
    Dim openWs As Worksheet, tmpSheet As Worksheet
    Dim rng As Range, VisbRange As Range
    Dim lRow As Long, date1  As Long, date2 As Long
    Dim Arr

    Set openWs = ThisWorkbook.Sheets("Sheet1")

    date1 = 20130101
    date2 = 20130107

    With openWs
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            lRow = 1
        End If

        Set rng = .Range("A1:F" & lRow)

        .AutoFilterMode = False

        With rng

            .AutoFilter Field:=1, Criteria1:= _
            ">=" & date1, Operator:=xlAnd, Criteria2:="<=" & date2

            .AutoFilter Field:=4, VisibleDropDown:=False
            .AutoFilter Field:=5, VisibleDropDown:=False
            .AutoFilter Field:=6, VisibleDropDown:=False

            Set VisbRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow

            Set tmpSheet = ThisWorkbook.Sheets.Add

            VisbRange.Copy tmpSheet.Rows(1)

            With tmpSheet
                lRow = .Range("A" & .Rows.Count).End(xlUp).Row

                Arr = .Range("A1:H" & lRow).Value
            End With

            On Error Resume Next
            Application.DisplayAlerts = False
            tmpSheet.Delete
            Application.DisplayAlerts = True
            On Error GoTo 0
        End With

        .AutoFilterMode = False
    End With
End Sub