Optimize Code

floppygoat

New Member
Joined
Jun 2, 2014
Messages
20
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.

VBA Code:
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

VBA Code:
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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
VBA Code:
Sub hide()
On Error Resume Next
Range("F2:F254").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End Sub
 

floppygoat

New Member
Joined
Jun 2, 2014
Messages
20
Thanks. I tried and does not work. This may be because the cell contains the following formula:

Excel 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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Would cell D2 be actually blank then? In that case you could just check that:
VBA Code:
Sub hide()
On Error Resume Next
Range("D2:D254").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End Sub
 

floppygoat

New Member
Joined
Jun 2, 2014
Messages
20

ADVERTISEMENT

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

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
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).

VBA Code:
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
 
Last edited:

JB2020

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
You could loop through each row and count blank cells, then hide each row where is greater than zero?

VBA Code:
    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

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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?

VBA Code:
    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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,846
Messages
5,627,226
Members
416,230
Latest member
jdaitchman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top