vba 使用vba查看和隐藏excel中的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23844501/
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
View and hide columns in excel using vba
提问by user3671568
I have a worksheet with values in columns B:G. In the same sheet in cell A1 I have made a drop down list using data validation with values like A, B and C.
我有一个工作表，在 B:G 列中有值。在单元格 A1 的同一张表中，我使用数据验证制作了一个下拉列表，其中包含 A、B 和 C 等值。
What I require is when I select cell value A then columns B:C need to be visible and the other columns should be hidden from D:G. In the same way if I select B from the list I need to view columns D:E and B:C and F:G should be hidden.
我需要的是当我选择单元格值 A 时，列 B:C 需要可见，其他列应该对 D:G 隐藏。同样，如果我从列表中选择 B，我需要查看列 D:E 和 B:C 和 F:G 应该隐藏。
Could you please help me on this.
你能帮我解决这个问题吗？
Note: I don't have good knowledge in VBA.
注意：我对 VBA 没有很好的了解。
回答by Alex P
Try this:
尝试这个：
- Open the VBA editor (
ALT + F11) - Double click
Sheet1 - Select
Worksheetin the top left drop down andChangein the top right hand drop down - Paste this code
- 打开 VBA 编辑器 (
ALT + F11) - 双击
Sheet1 - 选择
Worksheet左上角的下拉，Change在右上角的下拉 - 粘贴此代码
NB- this assumes data validation is in cell A1
注意-这假设数据验证在单元格中 A1
Private Sub Worksheet_Change(ByVal Target As Range)
Dim allColumns As Range
Set allColumns = Columns("B:G")
allColumns.Hidden = True
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Value = "A" Then
Columns("B:C").Hidden = False
ElseIf Target.Value = "B" Then
Columns("D:E").Hidden = False
ElseIf Target.Value = "C" Then
//Add more logic here
End If
End If
End Sub
回答by Derek
Go to view --> macros.
Hit the dropdown and do "record new macro".
Right click on a column header and do hide column.
Then do unhide column.
Do Macros->stop recording.
Macros-->View macros
Click edit.
转到查看 --> 宏。
点击下拉菜单并执行“录制新宏”。右键单击列标题并隐藏列。
然后取消隐藏列。做宏->停止录制。宏-->查看宏 单击编辑。
you get the following code:
你得到以下代码：
Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Selection.EntireColumn.Hidden = False
Now you know how to hide and show columns. First you select the column then your set Hidden = true or false.
现在您知道如何隐藏和显示列。首先选择列，然后设置 Hidden = true 或 false。
Google: excel macro when cell value changes
Google：单元格值更改时的 excel 宏
Click the first link: http://support.microsoft.com/kb/213612
单击第一个链接：http: //support.microsoft.com/kb/213612
Take the code from that link and read the comments:
从该链接中获取代码并阅读评论：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("A1:C10")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
' Display a message when one of the designated cells has been
' changed.
' Place your code here.
MsgBox "Cell " & Target.Address & " has changed."
End If
End Sub
Make sure you read the link very closely. And follow the instructions. I find I sometimes rush and miss important details
请务必仔细阅读链接。并按照说明进行操作。我发现我有时会匆忙而错过重要的细节
Let me know if this is enough or you need more help.
让我知道这是否足够，或者您需要更多帮助。
