英文:
Excel VBA - Excessive Time to Complete
问题
我创建了一个简单的电子表格，并使用一些VBA代码来根据列标题的颜色来隐藏/显示列。如果列标题是红色的，并且我想要隐藏它，那么我点击"隐藏"按钮。或者，如果我想让这些隐藏的列重新显示，我点击"取消隐藏"按钮。它可以正常工作，但问题是执行速度太慢。
这是隐藏的代码：
Sub HideColumnIfRed()
Dim c As Range
For Each c In Range("A:AT")
If c.Interior.Color = vbRed Then
c.EntireColumn.Hidden = True
End If
Next c
End Sub
这是取消隐藏的代码：
Sub UnhideColumnIfRed()
Dim c As Range
For Each c In Range("A:AT")
If c.Interior.Color = vbRed Then
c.EntireColumn.Hidden = False
End If
Next c
End Sub
你想要优化这个任务，有什么建议吗？
英文:
I created a simple spreadsheet with a bit of VBA code to hide/unhide based upon the color of the column header. If the column header is red, and I want it hidden, then I click the button "Hide". Alternatively, if I want those hidden columns to reappear, I click "Unhide". It works. Yay!
The actual task of unhiding/hiding works, the problem is that it takes entirely too long.
Here's the code for hiding:
Sub HideColumnIfRed()
Dim c As Range
For Each c In Range("A:AT")
If c.Interior.Color = vbRed Then
c.EntireColumn.Hidden = True
End If
Next c
End Sub
Here's the code for unhiding:
Sub HideColumnIfRed()
Dim c As Range
For Each c In Range("A:AT")
If c.Interior.Color = vbRed Then
c.EntireColumn.Hidden = False
End If
Next c
End Sub
What can I do to optimize this task?
Thank you in advance.
答案1
得分: 1
作为一个快速（也许有点粗糙）的解决方法，您可以像这样与您所需的列相交，使用的范围如下：
对于每个 c 在 Intersect(Range("A:AT"), ActiveSheet.UsedRange) 中
但要小心，因为 ActiveSheet 可能并不总是您期望的工作表。另一方面，您已经在没有引用的情况下使用了 Range("A:AT")，这已经引用了 ActiveSheet。
英文:
As a quick (maybe dirty) fix you could intersect your columns you are after with the used range like that
For Each c In Intersect(Range("A:AT"), ActiveSheet.UsedRange)
But be careful as ActiveSheet might not always the sheet you expect it to be. On the other hand you already use Range("A:AT") without reference which already refers to ActiveSheet anyway.
答案2
得分: 1
我明白了。我按照@BigBen的建议限制了单元格。
从这个：
For Each c In Range("A:AT")
变成了这个：
For Each c In Range("A2:AT2")
然后，我删除了相应的按钮，并重新创建了它们。现在一切都运行得很顺利。
英文:
I got it. I restricted the cells as suggested in @BigBen’s comment.
From this:
For Each c In Range("A:AT")
To this:
For Each c In Range("A2:AT2")
I then deleted the corresponding buttons, and recreated them. All works beautifully now.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论