VBA EXCEL基于单元格值隐藏行非常慢 [英] VBA EXCEL Hiding rows based on cell value is very slow

问题描述

我下面的代码根据相应的单元格值(如果为0，则隐藏)隐藏/取消隐藏整个行，并且工作正常.

I have the code below to hide/unhide entire rows depending on the corresponding cell value (hide if it's 0) and it works fine.

这是材料列表，并且有一个完成"按钮.在列表的最后，您按下按钮，任何数量为0的项目都应隐藏此相关行.

This is a list of material and there is a 'finalize' button. At the end of the list you press the button and any item that has a quantity = 0 should hide this relevant row.

工作正常.但是问题在于它非常慢.正如您所看到的，它是400多个行，而从字面上看，随着行的消失，我可以看到.它每秒处理大约20行，这使得在20秒内完成列表.而且列表每隔几个月就会翻一番.

It works fine. But the problem is that it's very slow. As you can see it's 400+ lines and I can literally see as the lines disappear. It's processing roughly 20 lines per second which makes it over 20 seconds to do the list. And the list will double every few months.

问题是-是否有其他方法可以立即或至少以比当前更快的速度隐藏相关行?

So the question is - is there any other method that will hide the relevant lines in an instant or at least faster that it currently is?

非常感谢！

隐藏:

Public Sub HideRows()
Dim cell As Range
    For Each cell In ActiveSheet.Range("H18:H469")
    cell.EntireRow.Hidden = (cell.Value = 0 And cell.Value <> "")
Next cell
End Sub

取消隐藏:

Public Sub UnhideRows()
Dim cell As Range
    For Each cell In ActiveSheet.Range("H18:H469")
    If (cell.Value = 0 And cell.Value <> "") Then cell.EntireRow.Hidden = False
    Next cell
End Sub

推荐答案

我只是按照注释中的内容输入内容.使用联盟收集合格范围并一口气躲起来.我不确定为什么要进行双重测试. = 0是否足够?或在@ Marcucciby2查询中，您是否打算使用Or?

I was just typing up as appeared in comments. Use Union to gather qualifying ranges and hide in one go. I am not sure why you are doing a double test. Wouldn't = 0 be sufficient? Or as @Marcucciby2 queries, did you intend an Or?

并且如其他答案中所述，您可以通过切换诸如ScreenUpdating，pageBreaks之类的内容并切换至手动计算模式来进行一些优化.

And as mentioned in other answer, you can do some optimization by switching of things like ScreenUpdating, pageBreaks and switch to manual calculation mode.

如果可能，请删除该ActiveSheet参考，并使用实际的工作簿和工作表参考.

If possible, get rid of that ActiveSheet reference and use the actual workbook and worksheet references.

Option Explicit
Public Sub HideRows()
    Dim cell As Range, unionRng As Range
    For Each cell In ActiveSheet.Range("H18:H469")
        If cell.Value = 0 Then
            If Not unionRng Is Nothing Then
                Set unionRng = Union(unionRng, cell)
            Else
                Set unionRng = cell
            End If
        End If
    Next
    If Not unionRng Is Nothing Then unionRng.EntireRow.Hidden = True
End Sub

这篇关于VBA EXCEL基于单元格值隐藏行非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！