根据单元格值隐藏行 [英] Hide rows based on cell value

问题描述

我有一个项目和数量表，当我要在数量为0时隐藏行。宏有效，但要花费太长时间才能完成。

这是代码：

  Sub Hide2ndFix（）
'
'Hide2ndFix Macro 
 '
 BeginRow = 414 
 EndRow = 475 
 ChkCol = 24 
 
对于RowCnt = BeginRow To EndRow 
如果Cells（RowCnt，ChkCol）.Value = 0然后
单元格（RowCnt，ChkCol）.EntireRow.Hidden = True 
结束如果
下一个RowCnt 
'
 End Sub 
  

如果列X中的值为0，是否有更有效的获取相同结果的方法，隐藏行414-475？

解决方案

通过禁用屏幕更新，使任何代码（对工作簿进行任何更改）的常见方法并禁用事件并将计算模式更改为手动（还有其他的方法，但是这3件事情是最大的因素）。

另一件事是通过收集一个联合范围内的所有行具有删除和插入行的重要因素，因为删除一行所需的时间与删除整个联合范围的时间相似。例如，如果删除一行需要1秒钟，那么删除1000行将需要1000秒，但删除包含1000行的联合范围只需要1秒钟。

尝试此代码：

  Sub Hide2ndFix（）
'
'Hide2ndFix宏
'
 Dim RowCnt As Long，uRng As Range 
 Application.ScreenUpdating = False 
 Application.EnableEvents = False 
应用程序。计算= xlCalculationManual 
 
 BeginRow = 414 
 EndRow = 475 
 ChkCol = 24 
 
对于RowCnt = BeginRow To EndRow 
如果单元格（RowCnt ，ChkCol）.Value = 0然后
如果uRng不是，然后
设置uRng =单元格（RowCnt，ChkCol）
 Else 
设置uRng = Union（uRng，Cells（RowCnt， ChkCol））
如果
 
结束If 
下一个RowCnt 
'
如果不是uRng没有，那么uRng.EntireRow.Hidden = True 
 
 Application.ScreenUpdating = True 
 Application.EnableEvents = True 
 Application.Calculation = xlCalculationAutomatic 
 
 End Sub 
  / pre> 
I have a table of items and quantities, where I want to hide rows when the quantity is 0. The macro works, but it takes too long to complete.

This is the code:
Sub Hide2ndFix()
'
' Hide2ndFix Macro
'
BeginRow = 414
EndRow = 475
ChkCol = 24

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = 0 Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
'
End Sub
Is there a more efficient way of getting the same result, of hiding rows 414-475 if the value in column X is 0?
解决方案
The common way to make any code (that does any changing to the workbook) faster is by disabling screen updating and disabling events and changing the calculation mode to Manual (there are other ways, but these 3 things have the biggest factor).

And the the other thing is by collecting all rows in one union range has a big factor in deleting and inserting rows because the time that is needed to delete one row is similar to the time for deleting the whole union range. For example if deleting one row needs 1 second then deleting 1000 rows will need 1000 seconds, but deleting a union range that contains 1000 rows only needs 1 second. 

Try this code:
Sub Hide2ndFix()
'
' Hide2ndFix Macro
'
Dim RowCnt As Long, uRng As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

BeginRow = 414
EndRow = 475
ChkCol = 24

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = 0 Then
         If uRng Is Nothing Then
          Set uRng = Cells(RowCnt, ChkCol)
         Else
          Set uRng = Union(uRng, Cells(RowCnt, ChkCol))
         End If

        End If
    Next RowCnt
'
 If Not uRng Is Nothing Then uRng.EntireRow.Hidden = True

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub


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