VBA 代码在几个工作表中隐藏许多固定的离散行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8740424/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA code to hide a number of fixed discrete rows across a few worksheets
提问by heavyarms
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
Sheets(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 行。我还想隐藏（为了争论），第 6 行、第 21 行和第 35-38 行在 Sheet2 中 - 我可以通过再重复上述代码行 3 次来做到这一点；但我相信有更好的方法可以做到这一点，就像学习练习一样。
Any help much appreciated :)
非常感谢任何帮助:)
Chris
克里斯
回答by chris neilsen
Specify a Unionof some ranges as follows
指定Union一些范围中的一个，如下所示
With Sheet1
Union(.Range("1:5"), .Rows(7), .Range("A10"), .Cells(12, 1)).EntireRow.Hidden = True
End With
回答by JMax
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 Unionrange from several worksheets, so you would have to loop over each worksheet argument.
但是您不能Union从多个工作表中选择范围，因此您必须遍历每个工作表参数。
回答by Tony Dallimore
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.
我加载了一个包含与我当前问题相关的参数字符串的数组，并编写了一个简单的循环来实现它们。查找 sub 和 function 声明并阅读 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
