Optimize Code

[floppygoat]

I have this code, which works, but it is incredibly slow. I have been trying to find a way to optimize it but can't. I thought about selecting all the rows first, and then hiding selection, but can't figure that out. I also thought about selecting/hiding ranges at time, rather than individual rows, but can't figure that out either. Any help would be appreciated.

Objective: Hide all rows within a range that contain "" within column 6.

Sub HIDE()
    
    Dim r1 As Range, c6 As Range

    Set r1 = Range("A2:F254")

    For Each c6 In r1
        If c6 = "" Then
            c6.EntireRow.Hidden = True
        Else
            c6.EntireRow.Hidden = False
        End If
    
    Next c6
    
    Range("A2").Select
    
End Sub

or

Sub HIDE()
    
    Dim r1 As Range, c6 As Range

    Set r1 = Range("A2:F254")

    For Each c6 In r1
        If c6 = "" Then
            c6.EntireRow.Select
            Selection.EntireRow.Hidden = True
        End If
    
    Next c6
    
    Range("A2").Select
    
End Sub

 

[Scott Huish]

Sub hide()
On Error Resume Next
Range("F2:F254").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End Sub

 

[floppygoat]

Thanks. I tried and does not work. This may be because the cell contains the following formula:

=IF(D2="","",SUM(C2:E2))

I did notice that this macro did hide rows 256 through 261 but nothing else which seemed a bit odd? The range of interest stops at 255.

 

[Scott Huish]

Would cell D2 be actually blank then? In that case you could just check that:

Sub hide()
On Error Resume Next
Range("D2:D254").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End Sub

 

[floppygoat]

No. Unfortunately, that is another cell that contains a formula that returns "".

 

[VBasic2008]

Hide Rows Containing a Specified Value in a Column (Range.Union)

This is a more efficient version than looping and hiding each found row, because it hides only once.
Probably an AutoFilter version could even be faster. But for this amount of rows, the solution presented will do (more than enough).

Option Explicit

Sub hideBlanksInColumnF()
 
    ' Define constants.
 
    Const Col As String = "F"
    Const First As Long = 2
    Const Criteria As String = ""
 
    ' Define Column Range.
 
    ' Define Last Non-Empty Cell.
    Dim cel As Range
    Set cel = Columns(Col).Find(What:="*", _
                                LookIn:=xlFormulas, _
                                SearchDirection:=xlPrevious)
    ' Define Column Range.
    Dim rng As Range
    Set rng = Range(Cells(First, Col), cel)
 
    ' 'Collect' cells containing Criteria in Hide Range.
 
    ' Declare variables.
    Dim hRng As Range ' Current Hide Range, Hide Range
    ' Iterate cells in Column Range.
    For Each cel In rng.Cells
        ' Check if Current Cell Range does not contain an error value.
        If Not IsError(cel) Then
            ' Check if value of Current Cell Range is equal to Criteria Value.
            If cel.Value = Criteria Then
                ' Check if current Hide Range does contain a range.
                If Not hRng Is Nothing Then
                    ' 'Combine' Current Cell Range with Current Hide Range.
                    Set hRng = Union(hRng, cel)
                Else
                    ' Assign Current Cell Range to Hide Range (only once).
                    Set hRng = cel
                End If
            End If
        End If
    Next cel
 
    ' Hide entire rows of Hide Range.
 
    ' Check if current Hide Range does contain a range.
    If Not hRng Is Nothing Then
        ' Hide entire rows of Hide Range.
        hRng.EntireRow.Hidden = True
    End If
 
End Sub

 

[JB2020]

You could loop through each row and count blank cells, then hide each row where is greater than zero?

    Dim r1 As Range, c6 As Range
    Set r1 = Range("A2:F254")

    r1.EntireRow.Hidden = False

    For Each c6 In r1.Rows
       If WorksheetFunction.CountBlank(c6) > 0 Then c6.EntireRow.Hidden = True
    Next c6

 

[JB2020]

You might need to switch out WorksheetFunction.CountBlank(c6) for WorksheetFunction.CountIf(c6, "") depending which version you are using.

You could loop through each row and count blank cells, then hide each row where is greater than zero?

    Dim r1 As Range, c6 As Range
    Set r1 = Range("A2:F254")

    r1.EntireRow.Hidden = False

    For Each c6 In r1.Rows
       If WorksheetFunction.CountBlank(c6) > 0 Then c6.EntireRow.Hidden = True
    Next c6