使用基于单元格值的 VBA 在 Excel 中隐藏列范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18095123/
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
Hiding Range of Columns in Excel using VBA based on cell Values
提问by user2659295
I need to hide a range of cells using a macro in excel. C11 contains the column index from where I need to start hiding the columns.
我需要在 excel 中使用宏隐藏一系列单元格。C11 包含我需要开始隐藏列的列索引。
Sub test()
Dim i As Integer
Dim j As Integer
Dim rocket As Range
i = Range("c11").Value
j = 12
rocket = Range(Cells(5, i), Cells(5, j))
Range("Rocket").Select
Selection.EntireColumn.Hidden = True
End Sub
The code is giving some unexpected error and as I am a novice, so have no clue what needs to be done..
代码给出了一些意外错误，因为我是新手，所以不知道需要做什么..
回答by Kazimierz Jawor
Tree steps to make your code working:
使您的代码工作的树步骤：
1st. Add Setkey word in appropriate line which is necessary:
第一。Set在适当的行中添加必要的关键字：
Set rocket = Range(Cells(5, i), Cells(5, j))
2nd. Rocket variablerepresents range, you will NOT need to call it in this way:
第二。Rocket variable代表范围，您不需要以这种方式调用它：
Range("Rocket")....
but
但
rocket....
3rd. Avoid Select methodand Selection objectalways when possible. Therefore the last two lines replace with this single one (which implements 2nd step, too):
第三。尽可能避免Select method并Selection object始终。因此，最后两行替换为这一行（也实现了第二步）：
rocket.EntireColumn.Hidden = true
回答by Brian
That last answer was awesome! Just for someone else's FYI, here is what worked in Excel 2007. The first line is always 3, but the ending line needed to be a variable. That's where I had the problem. THIS FIXED IT! The last 4 lines before the "End If" do the work. Hope this helps!
最后一个回答太棒了！仅供其他人参考，这是在 Excel 2007 中有效的方法。第一行始终为 3，但结束行需要是一个变量。这就是我遇到问题的地方。这解决了！“End If”之前的最后 4 行完成工作。希望这可以帮助！
Dim RowsToHide As Range
Dim RowHideNum As Integer
' Set Correct Start Dates for Billing in New File
Workbooks("----- Combined_New_Students_Updated.xlsx").Activate
Sheets("2015").Activate
StartDateLine1 = Format(START_DATE_1, "ww") - 1 ' Convert Start Date to Week Number
StartDateLine1 = (StartDateLine1 * 6) - 2 ' Convert Start Date to Line Number
If StartDateLine1 >= "10" Then
Cells(4, "q").Value = ""
Cells(StartDateLine1, "q").Value = STATUS_1
Cells(StartDateLine1, "z").Value = "START DATE " + START_DATE_1
RowHideNum = StartDateLine1 - 2
Set RowsToHide = Range(Cells(3, "a"), Cells(RowHideNum, "ab"))
RowsToHide.Select
RowsToHide.EntireRow.Hidden = True
End If
