AutoFilter return correct result, but when Special

Question

this is my code

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

The AutoFilter had show that I had 40 record, but when using .SpecialCells(xlCellTypeVisible), I just get about 20 record from it, after check through the record, I figure out that it always return the last range that match with date2, data after the range will just ignore, why?

//sample data after filter

filter range 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

Am I doing anything wrong? I thought excel will automatically return everything that visible after the filter, but it seems like it still apply the same filter? Any idea?

Update 1 More sample data, after apply Siddharth Rout code

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

Further to my comments, try this. This works for me.

EDIT: Just so that there is no confusion, what @mehow mentioned below is absolutely correct. The below is just a sample data. When you are actually working with data where you have to apply autofilter, use headers in 1st row and then use .Offset(1, 0).SpecialCells(xlCellTypeVisible) to get the filtered data as shown HERE

Code:

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

ScreenShots: (Before After)

Followup form comments:

Is this what you are trying?

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