以下是应用高级筛选器到Sheet1工作表列A（列表范围）的代码，它使用了Sheet2中值的范围（条件范围）：
Range("A1:A100").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Sheet2").Range("A1:A10"), Unique:=False
For i = 1 to maxRow
If Not ActiveSheet.Row(i).Hidden then
...do something that I need to do with that rows
EndIf
Next
在应用高级筛选后，有没有简单的属性可以给我一个可见行范围？
ActiveSheet.Range("A1:A100").Rows.SpecialCells(xlCellTypeVisible)
这将生成一个Range对象。
Lance的解决方案在大多数情况下都有效。
但是，如果您处理的是大型/复杂的电子表格，则可能会遇到“SpecialCells问题”。简而言之，如果创建的范围导致大于8192个非连续区域（这确实可能发生），则Excel在尝试访问SpecialCells时会抛出错误，导致代码无法运行。如果您的工作表足够复杂，您希望遇到此问题，则建议您坚持使用循环方法。
值得注意的是，这个问题并不是与SpecialCells属性本身有关，而是与Range对象有关。这意味着，每当您尝试获得一个可能非常复杂的范围对象时，您应该使用错误处理程序，或者像您已经做的那样，使程序对范围的每个元素进行操作（将范围拆分）。
另一个可能的方法是返回一组Range对象数组，然后通过数组循环。我已经发布了一些示例代码供测试。但是需要注意的是，只有在预计会出现上述问题或者您想要确保代码具有鲁棒性时，才真正需要使用此方法。否则，这只是不必要的复杂性。
Option Explicit
Public Declare Function GetTickCount Lib "kernel32" () As Long
Public Sub GenerateProblem()
'Run this to set up an example spreadsheet:
Dim row As Long
Excel.Application.EnableEvents = False
Sheet1.AutoFilterMode = False
Sheet1.UsedRange.Delete
For row = 1 To (8192& * 4&) + 1&
If row Mod 3& Then If Int(10& * Rnd) 7& Then Sheet1.Cells(row, 1&).value = "test"
Next
Sheet1.UsedRange.AutoFilter 1&, ""
Excel.Application.EnableEvents = True
MsgBox Sheet1.UsedRange.SpecialCells(xlCellTypeVisible).address
End Sub
Public Sub FixProblem()
'Run this to see various solutions:
Dim ranges() As Excel.Range
Dim index As Long
Dim address As String
Dim startTime As Long
Dim endTime As Long
'Get range array.
ranges = GetVisibleRows
'Do something with individual range objects.
For index = LBound(ranges) To UBound(ranges)
ranges(index).Interior.ColorIndex = Int(56 * Rnd + 1)
Next
'Get total address if you want it:
startTime = GetTickCount
address = RangeArrayAddress(ranges)
endTime = GetTickCount
Debug.Print endTime - startTime, ; 'Outputs time elapsed in milliseconds.
'Small demo of why I used a string builder. Straight concatenation is about
'10 times slower:
startTime = GetTickCount
address = RangeArrayAddress2(ranges)
endTime = GetTickCount
Debug.Print endTime - startTime
End Sub
Public Function GetVisibleRows(Optional ByVal ws As Excel.Worksheet) As Excel.Range()
Const increment As Long = 1000&
Dim max As Long
Dim row As Long
Dim returnVal() As Excel.Range
Dim startRow As Long
Dim index As Long
If ws Is Nothing Then Set ws = Excel.ActiveSheet
max = increment
ReDim returnVal(max) As Excel.Range
For row = ws.UsedRange.row To ws.UsedRange.Rows.Count
If Sheet1.Rows(row).Hidden Then
If startRow 0& Then
Set returnVal(index) = ws.Rows(startRow & ":" & (row - 1&))
index = index + 1&
If index > max Then
'Redimming in large increments is an optimization trick.
max = max + increment
ReDim Preserve returnVal(max) As Excel.Range
End If
startRow = 0&
End If
ElseIf startRow = 0& Then startRow = row
End If
Next
ReDim Preserve returnVal(index - 1&) As Excel.Range
GetVisibleRows = returnVal
End Function
Public Function RangeArrayAddress(ByRef value() As Excel.Range, Optional lowerindexRV As Variant, Optional upperindexRV As Variant) As String
'Parameters left as variants to allow for "IsMissing" values.
'Code uses bytearray string building methods to run faster.
Const incrementChars As Long = 1000&
Const unicodeWidth As Long = 2&
Const comma As Long = 44&
Dim increment As Long
Dim max As Long
Dim index As Long
Dim returnVal() As Byte
Dim address() As Byte
Dim indexRV As Long
Dim char As Long
increment = incrementChars * unicodeWidth 'Double for unicode.
max = increment - 1& 'Offset for array.
ReDim returnVal(max) As Byte
If IsMissing(lowerindexRV) Then lowerindexRV = LBound(value)
If IsMissing(upperindexRV) Then upperindexRV = UBound(value)
For index = lowerindexRV To upperindexRV
address = value(index).address
For char = 0& To UBound(address) Step unicodeWidth
returnVal(indexRV) = address(char)
indexRV = indexRV + unicodeWidth
If indexRV > max Then
max = max + increment
ReDim Preserve returnVal(max) As Byte
End If
Next
returnVal(indexRV) = comma
indexRV = indexRV + unicodeWidth
If indexRV > max Then
max = max + increment
ReDim Preserve returnVal(max) As Byte
End If
Next
ReDim Preserve returnVal(indexRV - 1&) As Byte
RangeArrayAddress = returnVal
End Function
Public Function RangeArrayAddress2(ByRef value() As Excel.Range, Optional lowerIndex As Variant, Optional upperIndex As Variant) As String
'Parameters left as variants to allow for "IsMissing" values.
'Code uses bytearray string building methods to run faster.
Const incrementChars As Long = 1000&
Const unicodeWidth As Long = 2&
Dim increment As Long
Dim max As Long
Dim returnVal As String
Dim index As Long
increment = incrementChars * unicodeWidth 'Double for unicode.
max = increment - 1& 'Offset for array.
If IsMissing(lowerIndex) Then lowerIndex = LBound(value)
If IsMissing(upperIndex) Then upperIndex = UBound(value)
For index = lowerIndex To upperIndex
returnVal = returnVal & (value(index).address & ",")
Next
RangeArrayAddress2 = returnVal
End Function
Excel.Range visibleRange = Excel.Application.ActiveWindow.VisibleRange