Excel VBA：隐藏所有列，然后取消隐藏某些列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27866689/
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
Excel VBA: Hide all columns, then unhide some columns
提问by phrebh
I hesitate to ask this because I do have a workaround, but I'd prefer a cleaner answer.
我犹豫要不要问这个，因为我有一个解决方法，但我更喜欢一个更清晰的答案。
I am using Excel 2010 and I have a procedure that does some basic formatting on a new sheet: hides all columns, sets the header row text, formats the header row, unhides the columns the header row uses. The problem is that the unhide doesn't quite work. After the procedure is run, the worksheet looks like all of the columns are still hidden, but if I resize the formula bar, the columns that the procedure unhid appear as I would expect. Even when the workbook is saved, closed, and reopened the columns don't appear until I resize the formula bar.
我正在使用 Excel 2010，我有一个程序可以在新工作表上执行一些基本格式设置：隐藏所有列、设置标题行文本、设置标题行格式、取消隐藏标题行使用的列。问题是取消隐藏不太有效。程序运行后，工作表看起来所有列仍然隐藏，但如果我调整公式栏的大小，程序取消隐藏的列会按照我的预期显示。即使工作簿被保存、关闭和重新打开，列也不会出现，直到我调整公式栏的大小。
I tried using DoEventsto refresh the screen. I tried setting Application.ScreenUpdatingto true even though I was never setting it to false. I even tried hiding and unhiding the formula bar through VBA. The only thing that works (my workaround) is to resize the formula bar as part of the procedure. It does work, but seems like it shouldn't be necessary. It might work to activate the range before I unhide it, but I prefer not to use Activateor Selectin VBA.
我尝试使用DoEvents刷新屏幕。Application.ScreenUpdating即使我从未将其设置为 false，我也尝试将其设置为 true。我什至尝试通过 VBA 隐藏和取消隐藏公式栏。唯一有效的方法（我的解决方法）是在过程中调整公式栏的大小。它确实有效，但似乎没有必要。在取消隐藏之前激活范围可能会起作用，但我不想在 VBA 中使用Activate或Select。
Any thoughts?
有什么想法吗？
Private Sub FormatSheet(sh As Worksheet)
Dim HeaderText As Variant
Dim EndCol As Long
Dim Header As Range
'header items for sheet
HeaderText = Array("DATE", "USER", "BC", "TC", "SUM")
'get last column index based on headers
EndCol = UBound(HeaderText) - LBound(HeaderText) + 1
With sh
'hide all columns in the sheet
.Columns.Hidden = True
'set the header range
Set Header = .Range(.Cells(2, 1), .Cells(2, EndCol))
'set the header text
Header = HeaderText
'set the header row formatting
With .Rows(2)
.Font.Bold = True
.Interior.Color = RGB(217, 217, 217)
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
End With
'unhide the columns used by the header
Header.EntireColumn.Hidden = False
'resize the formula bar to force the unhide to work
Application.FormulaBarHeight = 5
Application.FormulaBarHeight = 1
'autofit columns
.Columns.AutoFit
End With
End Sub
采纳答案by Jim Becker
LastCol = Range("A1").End(xlToRight).Column
LastCol = Range("A1").End(xlToRight).Column
With sh
与 sh
.Cells(1, EndCol + 1).Resize(, LastCol - EndCol).Columns.Hidden = True
End With
结束于
回答by Kyle Tegt
If you want it to unhide all cells:
如果您希望它取消隐藏所有单元格：
cells.EntireColumn.Hidden = False
And if you only want to unhide the 5 columns that are used in the header, then:
如果您只想取消隐藏标题中使用的 5 列，则：
Range(Cells(1, 1), Cells(1, EndCol)).EntireColumn.Select
This will only unhide the columns that are within the "Header", and it has to be put outside of the With statement to work (put it as the last line). It uses .select, I know, but thats the only way I can get it to work....
这只会取消隐藏“标题”内的列，并且必须将其放在 With 语句之外才能工作（将其作为最后一行）。它使用 .select，我知道，但这是我让它工作的唯一方法......
回答by Archimedes Trajano
The following will hide all the columns then selectively unhide.
以下将隐藏所有列，然后有选择地取消隐藏。
worksheet.Cells.EntireColumn.Hidden = true
worksheet.Cells(1,1).EntireColumn.Hidden = false
worksheet.Cells(1,2).EntireColumn.Hidden = false
noteThis only works with columns
注意这仅适用于列
worksheet.Cells.EntireRow.Hidden = true
Does not work.
不起作用。
