Macro running slow

[Robert1985]

Hi,

I have created a macro (below) to search a range and hide rows with no value, however, on running the macro it takes around a minute to run.
Is there anyway this can be sped up?

Dim xRg As Range
Application.ScreenUpdating = False
For Each xRg In Range("D1:D105")
If xRg.Value = "" Then
xRg.EntireRow.Hidden = True

Else
xRg.EntireRow.Hidden = False
End If
Next

End Sub


Thanks,

 

[steve the fish]

See if this is any quicker:

Dim xRg As Range, rng As Range, rngHide As Range, rngVis As Range
Dim arr, myVal as Long, i As Long

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

Set rng = Range("D1:D105")
myVal = rng.Cells(1).Row
arr = rng
  
For i = LBound(arr, 1) To UBound(arr, 1)
    Select Case Len(arr(i, 1))
        Case 0
            If Not rngHide Is Nothing Then
                Set rngHide = Union(rngHide, Rows(i + myVal - 1))
            Else
                Set rngHide = Rows(i + myVal - 1)
            End If
        Case Is > 0
            If Not rngVis Is Nothing Then
                Set rngVis = Union(rngVis, Rows(i + myVal - 1))
            Else
                Set rngVis = Rows(i + myVal - 1)
            End If
    End Select
Next
If Not rngHide Is Nothing Then rngHide.EntireRow.Hidden = True
If Not rngVis Is Nothing Then rngVis.EntireRow.Hidden = False

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

 

[Robert1985]

A lot quicker! Thank you very much