excel - 在多张纸上隐藏空白列

Question

我想隐藏多张纸上的空白列。我可以只在事件工作表上执行此操作，但是当我尝试使其适用于名称为一个月的所有工作表时，它不起作用。这是我到目前为止所拥有的:

Sub CommandButton1_Click()
  Dim col As Range
  Dim sheetsArray As Sheets
  Set sheetsArray = ActiveWorkbook.Sheets(Array("*Jan*", "*Feb*", "*Mar*", "*Apr*", "*May*", "*Jun*", "*Jul*", "*Aug*", "*Sep*", "*Oct*", "*Nov*", "*Dec*"))
  Dim sheet As Worksheet

  Application.ScreenUpdating = False
  For Each sheet In sheetsArray
   sheet.Columns.Hidden = False
        For Each col In sheet.UsedRange.Columns
          col.Hidden = sheet.col.Cells(Rows.Count, 1).End(xlUp).Row = 1
        Next col
  Next sheet

  Application.ScreenUpdating = True
End Sub

它现在也给我一个“方法或数据成员未找到错误”

Answer 1

Worksheet 类没有名为 col 的方法或数据成员。 .您可以删除 sheet.在 col 前面.此外，在模块顶部添加 Option Explicit ;然后，在运行代码之前，单击“调试”菜单，然后单击“编译”，以便及早发现此类问题。

除此之外，您必须根据您的名称过滤器检查每个工作表名称； ActiveWorkbook.Sheets不幸的是，集合不会神奇地解释数组中的过滤器。最后，你可以沿着这些路线走:

Option Explicit

Sub CommandButton1_Click()
    Dim sheet As Worksheet
    Dim col As Range
    Dim sheetNameFilters As Variant
    Dim filter As Variant

    sheetNameFilters = Array("*Jan*", "*Feb*", "*Mar*", "*Apr*", "*May*", "*Jun*", "*Jul*", "*Aug*", "*Sep*", "*Oct*", "*Nov*", "*Dec*")

    Application.ScreenUpdating = False

    For Each sheet In ThisWorkbook.Worksheets
        For Each filter In sheetNameFilters
            If sheet.Name Like filter Then
                sheet.Columns.Hidden = False

                For Each col In sheet.UsedRange.Columns
                    col.Hidden = (col.Cells(Rows.Count, 1).End(xlUp).Row = 1)
                Next

                Exit For
            End If
        Next
    Next

    Application.ScreenUpdating = True
End Sub