Excel VBA循环选择工作表

I'm trying to write a Macro that will loop through select amount of worksheets to hide empty rows on each sheet. In column "A" on each worksheet contains a 1 or a 0. If it's a 0 I want to hide the row.

我正在尝试编写一个宏,它将遍历选定数量的工作表以隐藏每个工作表上的空行。在每个工作表的“A”列中包含1或0.如果它是0我想隐藏该行。

Here's my code that I've scrapped together from various websites. My biggest challenge is knowing which objects I need to manipulate.

这是我从各个网站一起报废的代码。我最大的挑战是知道我需要操纵哪些物体。

enter code here
Public Sub HideRows()
Dim beginRow As Double
Dim endRow As Double
Dim ChkCol As Double
Dim RowCnt As Double
Dim ws As Worksheet
Dim ArrayOne As Variant
Dim InxW As Long

beginRow = 10
endRow = 185
ChkCol = 1

ArrayOne = Array("GB", "Adj. B", "Adj. F", "JC-Results", "PI-Results", "MK-Results", "TD-Results")


For InxW = LBound(ArrayOne) To UBound(ArrayOne)
    For RowCnt = beginRow To endRow
    If Cells(RowCnt, ChkCol).Value = 0 Then
        Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    Else
        Cells(RowCnt, ChkCol).EntireRow.Hidden = False
    End If
    Next RowCnt

Next



End Sub

2 个解决方案

#1

Try this:

Public Sub HideRows()
Dim beginRow As Double
Dim endRow As Double
Dim ChkCol As Double
Dim RowCnt As Double
Dim ws As Worksheet
Dim ArrayOne As Variant
Dim InxW As Long

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

beginRow = 10
endRow = 185
ChkCol = 1

ArrayOne = Array("GB", "Adj. B", "Adj. F", "JC-Results", "PI-Results", "MK-Results", "TD-Results")


For InxW = LBound(ArrayOne) To UBound(ArrayOne)
    With Sheets(ArrayOne(InxW))
        For RowCnt = beginRow To endRow
            If .Cells(RowCnt, ChkCol).Value = 0 Then
                .Rows(RowCnt).Hidden = True
            Else
                .Rows(RowCnt).Hidden = False
            End If
        Next RowCnt
    End With

Next InxW

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub

The main issue is that you are not telling Excel which sheet to search so it is searching only the active sheet as of the start of the code.

主要问题是您没有告诉Excel要搜索哪个工作表,因此它只搜索代码开头的活动工作表。

By putting everything in a With block and using the . in front of all range objects will tell excel which sheet to use.

通过将所有内容放入With块并使用。在所有范围对象的前面将告诉excel要使用哪个工作表。

Also turning of the calculations, screen updating and events off will help speed up the code, because it will not pause to do those things.

此外,关闭计算,屏幕更新和事件将有助于加快代码速度,因为它不会暂停执行这些操作。

#2

The AutoFilter method would make quick work of this. Opting to hide the dropdowns will closely mimic hiding the rows as well as addding additional methods of unhiding them.

AutoFilter方法可以快速完成这项工作。选择隐藏下拉列表将非常类似于隐藏行以及添加其他取消隐藏行的方法。

Public Sub HideRows()
    Dim beginRow As Long, endRow As Long, chkCol As Long
    Dim ndx As Long, arrOne As Variant

    Application.ScreenUpdating = False

    beginRow = 10
    endRow = 185
    chkCol = 1
    arrOne = Array("sheet1", "GB", "Adj. B", "Adj. F", "JC-Results", _
                     "PI-Results", "MK-Results", "TD-Results")

    For ndx = LBound(arrOne) To UBound(arrOne)
        With Worksheets(arrOne(ndx))
            If .AutoFilterMode Then .AutoFilterMode = False
            With .Cells(beginRow - 1, chkCol).Resize(endRow - beginRow + 2, 1)
                .Columns(1).AutoFilter Field:=1, Criteria1:="<>0", _
                                            VisibleDropDown:=False
                Debug.Print .Address
            End With
        End With
    Next ndx

    Application.ScreenUpdating = True

End Sub

There was no discussion on blanks but that could easily be added to Criteria2 with an XlAutoFilterOperator of xlOr.

没有关于空白的讨论,但可以使用xlOr的XlAutoFilterOperator轻松添加到Criteria2。

#1