根据单元格值隐藏行非常慢 [英] Hiding rows based on cell value is very slow

问题描述

我有工作代码根据相应的单元格值来隐藏/取消隐藏行.

I have working code to hide/unhide rows depending on the corresponding cell value.

这是材料列表，并且有一个完成"按钮.按下按钮，则应该隐藏数量= 0的任何行.

This is a list of materials and there is a 'finalize' button. You press the button and any row where quantity = 0 should be hidden.

有400多条线，我可以看到这些线消失了.它每秒处理大约20行，这使得在20秒内完成列表.该列表每隔几个月就会翻一番.

There are 400+ lines and I can see the lines disappear. It is processing roughly 20 lines per second which makes it over 20 seconds to do the list. The list will double every few months.

还有另一种方法可以更快地隐藏行吗?

Is there another method that will hide the lines faster?

隐藏:

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

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