VBA代码可以在几个工作表中隐藏许多固定的离散行

I'm for a solution to part of a macro I'm writing that will hide certain (fixed position) rows across a few different sheets. I currently have:

我正在寻找一个宏的部分解决方案我正在编写,它会隐藏几个不同工作表中的某些(固定位置)行。我目前有:

Sheets(Sheet1).Range("5:20").EntireRow.Hidden = True

表格(Sheet1).Range(“5:20”)。EntireRow.Hidden = True

To hide rows 5-20 in Sheet1. I also would like to hide (for arguements sake), row 6, row 21, and rows 35-38 in Sheet2 - I could do this by repeating the above line of code 3 more times; but am sure there's a better way of doing this, just as a learning exercise.

隐藏Sheet1中的行5-20。我也想隐藏(为了争论),第2行,第21行和Sheet2中的第35-38行 - 我可以通过重复上面的代码行3次来做到这一点;但我相信这是一种更好的方法,就像学习练习一样。

Any help much appreciated :)

任何帮助非常感谢:)

Chris

3 个解决方案

#1

Specify a Union of some ranges as follows

指定某些范围的联合,如下所示

With Sheet1
    Union(.Range("1:5"), .Rows(7), .Range("A10"), .Cells(12, 1)).EntireRow.Hidden = True
End With

#2

Here is a try:

这是一个尝试:

Sub hideMultiple()
    Dim r As Range
    Set r = Union(Range("A1"), Range("A3"))
    r.EntireRow.Hidden = True
End Sub

But you cannot Union range from several worksheets, so you would have to loop over each worksheet argument.

但是你不能在几个工作表中使用Union,因此你必须遍历每个工作表参数。

#3

This is a crude solution: no validation, no unhiding of existing hidden rows, no check that I have a sheet name as first parameter, etc. But it demonstrates a technique that I often find useful.

这是一个粗略的解决方案:没有验证,没有取消隐藏现有的隐藏行,没有检查我有一个工作表名称作为第一个参数,等等。但它演示了一种我经常觉得有用的技术。

I load an array with a string of parameters relevant to my current problem and code a simple loop to implement them. Look up the sub and function declarations and read the section on ParamArrays for a variation on this approach.

我加载一个数组,其中包含一系列与我当前问题相关的参数,并编写一个简单的循环来实现它们。查找子声明和函数声明,并阅读ParamArrays部分,了解此方法的变体。

Option Explicit
Sub HideColumns()

  Dim InxPL As Integer
  Dim ParamCrnt As String
  Dim ParamList() As Variant
  Dim SheetNameCrnt As String

  ParamList = Array("Sheet1", 1, "5:6", "Sheet2", 9, "27:35")

  SheetNameCrnt = ""

  For InxPL = LBound(ParamList) To UBound(ParamList)
    ParamCrnt = ParamList(InxPL)
    If InStr(ParamCrnt, ":") <> 0 Then
      ' Row range
      Sheets(SheetNameCrnt).Range(ParamCrnt).EntireRow.Hidden = True
    ElseIf IsNumeric(ParamCrnt) Then
      ' Single Row
      Sheets(SheetNameCrnt).Range(ParamCrnt & ":" & _
                                          ParamCrnt).EntireRow.Hidden = True
    Else
      ' Assume Sheet name
      SheetNameCrnt = ParamCrnt
    End If
  Next

End Sub

#1