PDA

View Full Version : Is there any way to make this faster



jammer6_9
03-01-2008, 07:02 AM
:banghead: or Entire Column A

Sub HideEmptyRow()
Dim Rng As Range
Set Rng = Range("A7:A2961")
For Each Rng In Rng
If Rng = "" Then
Rng.EntireRow.Hidden = Rng.Cells(1, 1).Value = ""
End If
Next Rng

End Sub

Bob Phillips
03-01-2008, 07:13 AM
Is this quicker



Sub HideEmptyRow()
Dim Rng As Range
Dim rngHide As Range

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Set Rng = Range("A7:A2961")
For Each Rng In Rng
Rng.EntireRow.Hidden = Rng.Cells(1, 1).Value = ""
Next Rng

With Application

.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub

jammer6_9
03-01-2008, 10:38 PM
You're the man xld... What more I can say :bow: Is that just because of ScreenUpdating?


Is this quicker



Sub HideEmptyRow()
Dim Rng As Range
Dim rngHide As Range

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Set Rng = Range("A7:A2961")
For Each Rng In Rng
Rng.EntireRow.Hidden = Rng.Cells(1, 1).Value = ""
Next Rng

With Application

.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub

Simon Lloyd
03-02-2008, 12:54 AM
Not wishing to step on toes but would this be faster?

Sub Hide_Cells()
With ActiveSheet
.Range("A1:A30").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End With
End Sub
you can always check for:

.SpecialCells(xlCellTypeConstants)
or

.SpecialCells(xlCellTypeFormulas)
before hiding the rows!

Maybe i have missed the point.

Simon Lloyd
03-02-2008, 12:56 AM
ooops!, must remember to read all of the problem....oh well, i will have woken up properly in a short while! :(