vba 如何在excel中获取所有过滤的数据行（带有隐藏列）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1296026/
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
How to get all filtered data rows in excel (with hidden columns)?
提问by PerlDev
My data sheet has filters and hidden columns. When filter(s) applies, I need to loop through all filtered data. I use: Excel.Range visibleRange = this.UsedRange.SpecialCells(XlCellType.xlCellTypeVisible, missing) as Excel.Range;
我的数据表有过滤器和隐藏列。当过滤器适用时，我需要遍历所有过滤后的数据。我使用： Excel.Range visibleRange = this.UsedRange.SpecialCells(XlCellType.xlCellTypeVisible, missing) as Excel.Range;
Now visibleRange.Rows.Count is 0; use foreach loop "foreach(Excel.Range row in visibleRange.Row)" row doesn't have all the columns, chopped from the first hidden column.
现在visibleRange.Rows.Count 为0；使用 foreach 循环 "foreach(Excel.Range row in visibleRange.Row)" 行没有所有的列，从第一个隐藏列中切出。
How could we loop through the filtered rows?
我们如何遍历过滤后的行？
回答by barrowc
I wouldn't use the SpecialCellsproperty at all. Just iterate through every row in the UsedRangeand check the Hiddenproperty as you go.
我根本不会使用该SpecialCells物业。只需遍历 中的每一行UsedRange并随时检查Hidden属性。
Not sure what language you are using but here's an example in VBA:
不确定您使用的是哪种语言，但这是 VBA 中的一个示例：
Dim rowIndex As Range
With Worksheets("Sheet1")
For Each rowIndex In .UsedRange.Rows
If (rowIndex.Hidden) Then
' do nothing - row is filtered out
Else
' do something
End If
Next rowIndex
End With
Each row (or rather each Rangeobject referenced by rowIndex) will contain all of the columns including the hidden ones. If you need to determine whether or not a column is hidden then just check the Hiddenproperty but remember that this only applies to entire columns or rows:
每一行（或者更确切地说是Range由 引用的每个对象rowIndex）将包含所有列，包括隐藏的列。如果您需要确定一列是否隐藏，则只需检查该Hidden属性，但请记住，这仅适用于整列或整行：
Dim rowIndex As Range
Dim colNumber As Integer
With Worksheets("Sheet1")
For Each rowIndex In .UsedRange.Rows
If (rowIndex.Hidden) Then
' do nothing - row is filtered out
Else
For colNumber = 1 To .UsedRange.Columns.Count
' Need to check the Columns property of the Worksheet
' to ensure we get the entire column
If (.Columns(colNumber).Hidden) Then
' do something with rowIndex.Cells(1, colNumber)
Else
' do something else with rowIndex.Cells(1, colNumber)
End If
Next colNumber
End If
Next rowIndex
End With
回答by Kyle
Thread Necro time. Here's what I do.
线程死灵时间。这就是我所做的。
'Count the total number of used rows in the worksheet (Using Column A to count on)
numFilteredCells = Application.WorksheetFunction.Subtotal(3, Range("A1:A" & Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row))
'Find Last filtered row with content
j = Range("A1").Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row
'Subtract the total number of filtered rows with content, + 1
jTargetDataRow = j - numFilteredCells + 1
jTargetDataRownow contains the first filtered row with content, jcontains the last, and numFilteredCellscontains the total number of filtered rows that have content.
jTargetDataRow现在包含第一个带内容的过滤行，j包含最后一个，并numFilteredCells包含包含内容的过滤行总数。
