选择每个工作表中第一个可见的单元格，并在VBA中进行循环 [英] Select the first visible cell in every sheet with a loop in VBA

问题描述

我使用下面的 VBA 在过滤范围内选择第一个可见单元格:

I use the below VBA to select the first visible cell in a filtered range:

Sub Postioning_Option_01()
Sheet1.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2).Select
End Sub

现在，我不想只将此VBA应用于Sheet1，而是要循环浏览所有工作表.
因此，我尝试使用此VBA:

Now, instead of only applying this VBA to Sheet1 I want to loop through all sheets.
Therefore, I tried to go with this VBA:

Sub Postioning_Option_02()
Dim b As Worksheet
For Each b In Worksheets
b.Select
b.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2).Select
Next b
End Sub

运行此VBA时，出现错误Object variable or With block variable not set.
我需要在VBA中进行哪些更改才能使其正常工作?

When I run this VBA I get error Object variable or With block variable not set.
What do I need to change in the VBA to make it work?

推荐答案

这将跳过没有自动过滤器的工作表:

This will skip sheets with no autofilter:

Sub Postioning_Option_02()

    Dim b As Worksheet
    For Each b In Worksheets
        If b.AutoFilterMode Then
            b.Select
            b.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2).Select
        End If
    Next b

End Sub

这篇关于选择每个工作表中第一个可见的单元格，并在VBA中进行循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！