用于隐藏行的慢VBA循环 [英] Slow VBA loop for hiding rows

问题描述

下面的代码用于循环遍历区域中的所有行，并根据该单元格及其下面的单元格中的单元格的值隐藏它们。如果两个单元格值都=&q；&q；，则意图隐藏整行。一切都很好，但速度非常慢。任何有关更快的建议都将不胜感激。

Sheets("Morning Report Export Sheet").Activate

For x = 10 To 108
    If Cells(x, 9).Value = "" Then
        If Cells(x + 1, 9).Value = "" Then
            Cells(x, 9).EntireRow.Hidden = True
        End If
    End If
Next

.我也尝试过以下方法，但速度一样慢...

If Cells(x, 9).Value = "" And Cells(x + 1, 9).Value = "" Then

推荐答案

我写了两个版本，因为我很无聊。

这是@MathieuGuindon提到的数组方法

Sub HideRowsUsingArrays()
    Dim x As Long, HideRows As Range
    Dim StartRow As Long, EndRow As Long, Col As Long
    
    'TARGET RANGE
    Col = 9
    StartRow = 10
    EndRow = 108
    'TARGET RANGE
    
    Dim sh As Worksheet
    Set sh = Sheets("Morning Report Export Sheet")
    
    Dim vArr() As Variant
    'Saving all values in the target range to an array
    vArr = sh.Cells(StartRow, Col).Resize(EndRow).Value
    
    'Looping through the array
    For x = LBound(vArr) To UBound(vArr) - 1
        'If val or next val is empty
        If vArr(x) = "" And vArr(x + 1) = "" Then
            'Add the corresponding row to HideRows range
            'Union causes an error if HideRows is nothing, so the first iteration cant use Union
            If HideRows Is Nothing Then
                Set HideRows = sh.Rows(x + StartRow - 1).EntireRow
            Else
                Set HideRows = Union(HideRows, sh.Rows(x + StartRow - 1).EntireRow)
            End If
        End If
    Next x
    
    'Hide the gathered rows
    If Not HideRows Is Nothing Then HideRows.EntireRow.Hidden = True
End Sub

这是@TimWilliams链接到的Range方法

Sub HideRowsUsingRanges()
    Dim cell As Range, HideRows As Range
    Dim StartRow As Long, EndRow As Long, Col As Long
    
    'TARGET RANGE
    Col = 9
    StartRow = 10
    EndRow = 108
    'TARGET RANGE
    
    Dim sh As Worksheet
    Set sh = Sheets("Morning Report Export Sheet")
    
    Dim r As Range
    'Saving the target range
    set r = sh.Cells(StartRow, Col).Resize(EndRow)
    
    'Looping through each cell of the range
    For Each cell In r
        'If val or next val is empty
        If cell.Value = "" And cell.Offset(1).Value = "" Then
            'Add the corresponding row to HideRows range
            'Union causes an error if HideRows is nothing, so the first iteration cant use Union
            If HideRows Is Nothing Then
                Set HideRows = cell
            Else
                Set HideRows = Union(HideRows, cell)
            End If
        End If
    Next cell
    
    'Hide the gathered rows
    If Not HideRows Is Nothing Then HideRows.EntireRow.Hidden = True
End Sub

这篇关于用于隐藏行的慢VBA循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！