我有一個 excel 表（VBA - Office 2013），我在其中設定了自動過濾器。然后我的腳本讀取一些可見的行并將其復制到另一個作業表。
With stacked_sheet
.AutoFilterMode = False
.Range("A1:I1000").AutoFilter Field:=6, Criteria1:=uBoards(b, 1)
.Range("A1:I1000").AutoFilter Field:=9, Criteria1:=uCombos(c, 1)
End With
'Counting number of rows post filtering based on Column 9 (boards)
filtered_row_count = stacked_sheet.Range("A2:I1000").Columns(9).SpecialCells(xlCellTypeVisible).Count
只要過濾器后有記錄，此腳本就能按預期作業。但是，當過濾器后沒有記錄時，' filtered_row_count ' 總是回傳 1 而不是 0。
下圖顯示了記錄后過濾器（無記錄）
任何幫助表示贊賞。我也嘗試了以下變體，但都沒有奏效。. .
1. filtered_row_count = stacked_sheet.Range("A2:1000").Rows.SpecialCells(xlCellTypeVisible).Count
2. filtered_row_count = stacked_sheet.Range("A2:1000").Columns.SpecialCells(xlCellTypeVisible).Count
3. filtered_row_count = stacked_sheet.Range("A2:1000").Cells.SpecialCells(xlCellTypeVisible).Count
uj5u.com熱心網友回復：
復制SpecialCells( WorksheetFunction.SubTotal)
鏈接（微軟）
VBA：Range.SpecialCells method (Excel)VBA：WorksheetFunction.Subtotal method (Excel)Excel：SUBTOTAL function
提示
- 在應用過濾器之前創建必要的范圍參考。
- 如果您按非空值
WorksheetFunction.SubTotal進行過濾，您可以使用來計算過濾單元格的數量，但您希望在 的一列（過濾列 (Field)）中進行Data Range，該范圍沒有標題。請注意，您始終可以按照“On Error Resume Next方式”進行操作（第二個示例）。 - 確定單元格數量大于 0 后，您可以繼續復制
.SpecialCells(xlCellTypeVisible)應用到Data Range，或者如果需要復制標題，則應用到整個范圍。 - 這些示例中的一些范圍參考可能是多余的，但保留在代碼中以更好地理解應考慮的內容。一旦你決定走哪條路，洗掉多余的參考。
- 在第二個示例中，重點是獲取單元格的數量。在
On Error...代碼塊中，您通常會使用Set sfdrg = sdrg.SpecialCells(xlCellTypeVisible)，因為您不關心單元格的確切數量。您只需要知道是否有任意數量的單元格，這由If Not sfdcrg Is Nothing Then(通常If Not sfdrg Is Nothing Then)確定。
Option Explicit
Sub FilterRangeSubTotal()
Dim srg As Range ' Source Range
Dim sdrg As Range ' Source Data Range (Source Range Without Headers)
Dim sfdrg As Range ' Source Filtered Data Range
Dim sdcrg As Range ' Source Data Column Range
Dim sfdcrg As Range ' Source Filtered Data Column Range
With StackedSheet
If .AutoFilterMode Then .AutoFilterMode = False
Set srg = .Range("A1:I1000")
Set sdrg = srg.Resize(srg.Rows.Count - 1).Offset(1) ' "A2:I1000"
' To be able to use 'WorksheetFunction.SubTotal', pick the column
' where values in all filtered cells are ensured,
' i.e. where you do not filter by blanks e.g.:
Set sdcrg = sdrg.Columns(9) ' "I2:I1000"
End With
srg.AutoFilter Field:=6, Criteria1:=uBoards(b, 1) ' ???
srg.AutoFilter Field:=9, Criteria1:=uCombos(c, 1) ' ???
Dim FilteredCellsCount As Long
FilteredCellsCount = WorksheetFunction.Subtotal(103, sdcrg) ' COUNTA
If FilteredCellsCount > 0 Then
'Set sfdrg = sdrg.SpecialCells(xlCellTypeVisible)
'sfdrg.Copy AnotherSheet.Range("A2")
Set sfdcrg = Nothing
'Else 'FilteredCellsCount = 0
End If
StackedSheet.AutoFilterMode = False
MsgBox "Filtered Cells Count: " & FilteredCellsCount
End Sub
Sub FilterRangeOnErrorResumeNext()
Dim srg As Range ' Source Range
Dim sdrg As Range ' Source Data Range (Source Range Without Headers)
Dim sfdrg As Range ' Source Filtered Data Range
Dim sdcrg As Range ' Source Data Column Range
Dim sfdcrg As Range ' Source Filtered Data Column Range
With StackedSheet
If .AutoFilterMode Then .AutoFilterMode = False
Set srg = .Range("A1:I1000")
Set sdrg = srg.Resize(srg.Rows.Count - 1).Offset(1) ' "A2:I1000"
Set sdcrg = sdrg.Columns(9) ' "I2:I1000", but you can pick any (valid)
End With
srg.AutoFilter Field:=6, Criteria1:=uBoards(b, 1) ' ???
srg.AutoFilter Field:=9, Criteria1:=uCombos(c, 1) ' ???
' 'Rows.Count' will not work because the range is possibly non-contiguous
' and only the rows of the first area will be considered.
' So you have to use 'Cells.Count' in one column only (sdcrg).
' It will never be 0, because if no cell, an error will occur.
On Error Resume Next
Dim sfdcrg As Range: Set sfdcrg = sdcrg.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
Dim FilteredCellsCount As Long
If Not sfdcrg Is Nothing Then
FilteredCellsCount = sfdcrg.Cells.Count ' not 'sfdcrg.rows.count'
'Set sfdrg = sdrg.SpecialCells(xlCellTypeVisible)
'sfdrg.Copy AnotherSheet.Range("A2")
Set sfdcrg = Nothing
'Else
'FilteredCellsCount = 0
End If
StackedSheet.AutoFilterMode = False
MsgBox "Filtered Cells Count: " & FilteredCellsCount
End Sub
uj5u.com熱心網友回復：
解決方法是將 (A2:I1000) 更改為 (A1:I1000)
filtered_row_count = stacked_sheet.Range("A1:I1000").Columns(9).SpecialCells(xlCellTypeVisible).Count
下面選擇的解決方案對其進行了詳細描述。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/329193.html