Excel VBA – 完成所需时间过长

huangapple go评论72阅读模式
英文:

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.

snip of project

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.

huangapple
  • 本文由 发表于 2023年5月11日 00:47:43
  • 转载请务必保留本文链接:https://go.coder-hub.com/76220854.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定