vba AutoFilter 返回正确的结果，但是当 SpecialCells(xlCellTypeVisible).value 时，excel 只返回一半的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19521105/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
AutoFilter return correct result, but when SpecialCells(xlCellTypeVisible).value , excel only return half of the record
提问by Se0ng11
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?
AutoFilter 显示我有 40 条记录，但是当使用 .SpecialCells(xlCellTypeVisible) 时，我只从中得到大约 20 条记录，在检查记录后，我发现它总是返回与 date2、data 匹配的最后一个范围范围后会忽略，为什么？
//sample data after filter
//过滤后的样本数据
filter range 20130101 - 20130107
过滤范围 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?
我做错了什么吗？我认为 excel 会自动返回过滤器后可见的所有内容，但它似乎仍然应用相同的过滤器？任何的想法？
Update 1More sample data, after apply Siddharth Rout code
更新 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
回答by Siddharth Rout
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
编辑：为了避免混淆，下面提到的@mehow 是绝对正确的。下面只是一个示例数据。当您实际处理必须应用自动.Offset(1, 0).SpecialCells(xlCellTypeVisible)过滤器的数据时，请在第一行中使用标题，然后使用此处获取过滤后的数据
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
