如何用UsedRange动态获取排除首行的非连续筛选可见区域?
筛选后获取除首行外可见区域的VBA问题

如示例图所示,我在B列设置了自动筛选并排除值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 '额外添加了行到rng ($A$2:$B$3,$A$6:$B$8) Set rng = Intersect(ws.Cells, ws.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible)) Debug.Print rng.Address '额外添加了行到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) '错误: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
提前感谢各位的帮助和建议。
内容的提问来源于stack exchange,提问作者Leedo
相关产品推荐
相关产品推荐

