AutoFilter返回正确的结果，但是当SpecialCells（xlCellTypeVisible）.value，excel只返回一半的记录 [英] AutoFilter return correct result, but when SpecialCells(xlCellTypeVisible).value , excel only return half of the record
问题描述
这是我的代码
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
AutoFilter显示我有40条记录，但是当使用.SpecialCells（xlCellTypeVisible）时，我只需从它获得约20条记录，通过记录检查后，我发现它总是返回与date2匹配的最后一个范围，范围之后的数据将忽略，为什么？
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
过滤范围20130101 - 20130107
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
我做错了吗？我以为excel会自动返回过滤器后可见的一切，但是它似乎仍然应用相同的过滤器？任何想法？
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?
更新1
应用Siddharth Rout代码后，更多示例数据
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
推荐答案
除了我的评论，请试试看。这对我来说很有用。
Further to my comments, try this. This works for me.
编辑：只是这样没有混淆，下面提到的@mehow是绝对正确的。下面只是一个示例数据。当您实际处理必须应用自动过滤器的数据时，请使用第1行中的标题，然后使用 .Offset（1，0）.SpecialCells（xlCellTypeVisible）获取过滤数据如下所示 HERE
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
代码
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
这篇关于AutoFilter返回正确的结果，但是当SpecialCells（xlCellTypeVisible）.value，excel只返回一半的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
