在 Excel 2013 中使用 VBA 读取隐藏列的问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18338904/
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
Issue reading hidden column using VBA in Excel 2013
提问by JeffreDodd
I am currently having issues with a Macro I am programming for Excel 2013 regarding reading hidden columns. I am trying to utilize Column A as a row of unique keys to allow me to quickly develop logic that hides and shows a row based on the key value in column A. When I hide column A manually in the sheet for visual purposes I am then unable to read from that column, aka my code returns an error. If I show the column the code executes clearly. Thanks in advance for the help!
我目前遇到有关读取隐藏列的宏的问题，我正在为 Excel 2013 编程。我正在尝试将 A 列用作一行唯一键，以允许我快速开发基于 A 列中的键值隐藏和显示一行的逻辑。当我出于视觉目的在工作表中手动隐藏 A 列时无法从该列中读取，也就是我的代码返回错误。如果我显示该列，代码会清楚地执行。在此先感谢您的帮助！
Public Sub hideRow(findId As String, sheetName As String)
Dim lastRow As Long
Dim foundCell As Range
Dim hideThisRowNum As Integer
'Get Last Row
lastRow = Worksheets(sheetName).Range("A" & Rows.Count).End(xlUp).Row
'Find ID
With Worksheets(sheetName).Range("a1:a1000") 'This needs to be A1 to AxlDown
Set foundCell = Worksheets(sheetName).Range("A1:A" & lastRow).Find(What:=findId, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
End With
'Get Row # to Hide
hideThisRowNum = Val(foundCell.Row)
'Hide row
Worksheets(sheetName).Rows(hideThisRowNum).Hidden = True
'Set Add To Action Plan = No
Worksheets(sheetName).Range("G" & hideThisRowNum).Value = "No"
End Sub
回答by Joe
The problem is in the .Find() call. Using LookIn:=xlValueswon't find hidden cells. Change it to LookIn:=xlFormulasand it should work.
问题出在 .Find() 调用中。使用LookIn:=xlValues不会找到隐藏的单元格。将其更改为LookIn:=xlFormulas它应该可以工作。
