正如您在示例圖片中看到的那樣，我在B列上設定了自動過濾器并排除了 value B。
我需要使用UsedRange和 SpecialCells(xlCellTypeVisible) 方法獲取除第一行之外的可見范圍。
我嘗試了以下三個代碼，但它要么添加額外的行，要么引發錯誤：
Sub Get_Range_of_two_non_contiguous_filtered_criteria_except_First_Row()
Dim ws As Worksheet, rng As Range
Set ws = ThisWorkbook.ActiveSheet
Set rng = ws.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible)
Debug.Print rng.Address 'Addtional Row is added to rng ($A$2:$B$3,$A$6:$B$8)
Set rng = Intersect(ws.Cells, ws.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible))
Debug.Print rng.Address 'Addtional Row is added to rng $A$2:$B$3,$A$6:$B$8
Dim crg As Range: Set crg = ws.UsedRange.SpecialCells(xlCellTypeVisible)
Set crg = crg.Offset(1, 0).Resize(crg.Rows.Count - 1, crg.Columns.Count) 'Error: Application-defined or object-defined error
Debug.Print crg.Address
End Sub
這是我發現它可以正常作業的唯一方法：
Dim LastRow As Long
LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = ws.Range("A2:B" & LastRow).SpecialCells(xlCellTypeVisible)
Debug.Print rng.Address
提前感謝您的所有幫助和評論。
uj5u.com熱心網友回復：
下一部分是錯誤的：
Dim crg As Range: Set crg = ws.UsedRange.SpecialCells(xlCellTypeVisible)
Set crg = crg.Offset(1, 0).Resize(crg.Rows.Count - 1, crg.Columns.Count) 'Error: Application-defined or object-defined error
Debug.Print crg.Address
您需要在使用之前調整使用范圍的大小SpecialCells。不能以這種方式調整不連續范圍的大小。
下一個改編的代碼應該可以作業：
Dim crg As Range: Set crg = ws.UsedRange
Dim crgVS As Range
Debug.Print crg.Resize(crg.rows.count - 1, crg.Columns.count).Offset(1).address
Set crgVS = crg.Resize(crg.rows.count - 1, crg.Columns.count).Offset(1).SpecialCells(xlCellTypeVisible)
Debug.Print crgVS.address
我會（僅）建議您使用Range("A1").CurrentRegion. UsedRange在處女/新紙上效果很好。它可能包括您以前使用它們的行/列，即使它們現在是空的。嘗試為現有范圍下方的內部或單元格著色并檢查UsedRange地址。在這種情況下的結果可能無法滿足您的期望......
uj5u.com熱心網友回復：
迭代這些領域的作品：
Dim crg as range, a As Range
With ws.UsedRange.SpecialCells(xlCellTypeVisible)
For Each a In .Areas
If Not crg Is Nothing Then
Set crg = Union(crg, a)
Else
'this area must include the first row -> remove
Set crg = a.Offset(1).Resize(a.Rows.Count - 1)
End If
Next
End With
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/519615.html
標籤：擅长vba自动过滤器