Hide rows in EXCEL

[Marc H.]

Hi Folks,

I`ve got a little problem in excel and VBA.

I´va to check more then 5000 cells if they are empty or not. In case they
are empty I want to hide the row.

Here my is code:

for z = 1 to 5000
a = Cells(z, 1)
If a = 0 Then
Rows(t).Hidden = True
End If
next z

It´s running well but to slow.

Does anyone of you knows a better quicker way?

thanx a lot.
Marc

 

[Gary''s Student]

This may run a bit faster:

Sub servient()
Dim r As Range
Set r = Nothing
For z = 1 To 5000
a = Cells(z, 1)
If a = 0 Then
If r Is Nothing Then
Set r = Cells(z, 1)
Else
Set r = Union(r, Cells(z, 1))
End If
End If
Next z
r.EntireRow.Hidden = True
End Sub

 

[JE McGimpsey]

One way:

Public Sub HideBlanks()
Dim rBlanks As Range
With Columns(1).Cells
.EntireRow.Hidden = False
On Error Resume Next
Set rBlanks = .SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
End With
If Not rBlanks Is Nothing Then _
rBlanks.EntireRow.Hidden = True
End Sub

 

[Rick Rothstein]

I'm not sure if this will be faster or not, but (as long as the cell contain nothing, that is, no data or formulas) it does reduce the code to a one-liner...

Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

If it is still slow, you could try turning off screen updating... that might help...

Application.ScreenUpdating = False
Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = True

 

[Marc H.]

Hi Rick,

I don´t want to delete the rows but disabling the screen update is it.

It´t wonderful.

thanx



Newsbeitrag I'm not sure if this will be faster or not, but (as long as the cell contain
nothing, that is, no data or formulas) it does reduce the code to a
one-liner...

Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

If it is still slow, you could try turning off screen updating... that might
help...

Application.ScreenUpdating = False
Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = True

 

[Rick Rothstein]

Sorry, misread your post. You can still use the single-line code to hide your rows too...

Application.ScreenUpdating = False
Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
Application.ScreenUpdating = True