# Optimize Code

#### floppygoat

##### New Member
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``````

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Scott Huish

##### MrExcel MVP
VBA Code:
``````Sub hide()
On Error Resume Next
Range("F2:F254").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End Sub``````

#### floppygoat

##### New Member
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
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

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

#### VBasic2008

##### Board Regular
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
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
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``````

Replies
14
Views
78
Replies
8
Views
172
Replies
0
Views
47
Replies
3
Views
47
Replies
1
Views
55

### Forum statistics

1,127,633
Messages
5,626,004
Members
416,151
Latest member
Openminded intellectual

### 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?

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