英文:
Hiding columns without using a loop
问题
我有一个使用For循环根据单元格值隐藏列的代码。这段代码在工作簿中的7个工作表中使用，该工作簿总共有17个工作表（这个信息稍后会有关联）。
Private Sub Worksheet_calculate()
Application.EnableEvents = False
On Error GoTo errorHandling
Dim controlCell As Range, tableToHide As Range
Set controlCell = Range("C12") '单元格包含从另一个工作表中的单元格中检索的值的公式
Set tableToHide = Range("Table1") '表格的名称，根据controlCell的值来显示/隐藏列
tableToHide.EntireColumn.Hidden = False
For i = controlCell.Value To tableToHide.Columns.Count
tableToHide.Columns(i).EntireColumn.Hidden = True
Next i
errorHandling:
On Error GoTo 0
Application.EnableEvents = True
End Sub
我正在寻找一种在不使用循环的情况下隐藏列的方法，或者一种更改此循环的方法。希望进行更改的原因是，使用当前形式时，更改工作簿的任何单元格都会导致加载旋转图标显示几秒钟。可以想象，这对用户体验不是很好。
英文:
I have code that uses a For loop to hide a number of columns based on a Cell value. The code is used in 7 sheets out of a Workbook that has 17 sheets in total (this information is relevant later).
Private Sub Worksheet_calculate()
Application.EnableEvents = False
On Error GoTo errorHandling
Dim controlCell As Range, tableToHide As Range
Set controlCell = Range("C12") 'Cell contains a formula to retrieve the value from a cell in a seperate sheet
Set tableToHide = Range("Table1") 'The name of the table where columns need to be shown/hidden based on controlCell value
tableToHide.EntireColumn.Hidden = False
For i = controlCell.Value To tableToHide.Columns.Count
tableToHide.Columns(i).EntireColumn.Hidden = True
Next i
errorHandling:
On Error GoTo 0
Application.EnableEvents = True
End Sub
I'm looking for a way to hide the columns without using a loop or a way to change this loop. The reason for wanting the change is because when this is used in its current form, changing any cell throughout the Workbook's 17 sheets results in a loading spinner showing for a few seconds. As you can imagine, that is not a great user experience.
答案1
得分: 3
你可以一次性隐藏所有列。有多种方法可以做到，例如：
Dim startCol As Long
startCol = controlCell.value
Dim hideRange As Range
Set hideRange = tableToHide.Cells(1, startCol).Resize(1, tableToHide.Columns.Count - startCol + 1)
hideRange.EntireColumn.Hidden = True
英文:
You can hide all columns at once. Various ways to do so, eg
Dim startCol As Long
startCol = controlCell.value
Dim hideRange As Range
Set hideRange = tableToHide.Cells(1, startCol).Resize(1, tableToHide.Columns.Count - startCol + 1)
hideRange.EntireColumn.Hidden = True
答案2
得分: 0
我不明白为什么你在“Worksheet_calculate（）”事件中放置了这段代码：这会导致每次进行计算时都要检查整个工作簿。
为什么你不把那个for循环放在另一个宏中，可以按需运行，并且只在“Worksheet_calculate（）”事件中检查你实际计算的列？
英文:
I don't understand why you have this code inside the Worksheet_calculate() event: this causes the entire workbook to be checked every time a calculation is made.
Why don't you put that for-loop inside another macro, which you can run on demand, and use the Worksheet_calculate() event only to check the column you're actually calculating?
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论