vba 当单元格值为 #N/A 时如何隐藏列 (G-AZ)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10095024/
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
How to hide columns (G-AZ) when cell value is #N/A
提问by user1324883
I currently have a vlookup populating row 1 (cells G1-AZ1) with titles, and would like to hide the Columns(G1-AZ1) if the vlookup pulls back nothing/#N/A. I know this is a simple macro but I'am new to VBA and I have had zero luck searching the web. Thanks!
我目前有一个 vlookup 用标题填充第 1 行（单元格 G1-AZ1），如果 vlookup 没有拉回任何内容/#N/A，我想隐藏列（G1-AZ1）。我知道这是一个简单的宏，但我是 VBA 的新手，我在网上搜索的运气为零。谢谢！
回答by Andrew
I usually place such formulas in ISNA()and then just use Excel filter to hide empty rows
我通常将这样的公式放在ISNA() 中，然后只使用 Excel 过滤器来隐藏空行
=IF(ISNA(VLOOKUP(A3,G1:H7,2,FALSE)),"",VLOOKUP(A3,G1:H7,2,FALSE))
回答by chris neilsen
Try this:
尝试这个：
- Loop throught he header cells
- Set the
EntireColumn.Hiddenproperty based on your criteria - Use
.ScreenUpdating = Falseto prevent screen flicker and speed it up
- 循环遍历标题单元格
EntireColumn.Hidden根据您的标准设置属性- 使用
.ScreenUpdating = False以防止屏幕闪烁和加快速度
Sub HideColumns()
Dim rng As Range
Dim cl As Range
Application.ScreenUpdating = False
Set rng = [G1:AZ1]
For Each cl In rng
If IsError(cl) Then
cl.EntireColumn.Hidden = cl = CVErr(xlErrNA)
Else
cl.EntireColumn.Hidden = cl = ""
End If
Next
Application.ScreenUpdating = True
End Sub
