hide rows if cell in a column has no value

[bernd]

HI,

I found this code in another thread and edited it for the range. The
code works fine if nothing is in the cell. But in my cells there is a
formula. The results of these formulas (value of a cell on another
sheet). Sometime these values era simply nothing, but the code doesn't
hide the rows. It onlys hides the rows if everything is cleared. How
can this code be edited, so that the rows witk cells that have no
value (like "") ere hidden?

Sub HideRows()
Sheets("Shortlist").Select
Range("J8:eindprioriteit").EntireRow.Hidden = False
On Error Resume Next
Set rng = Range("J7:eindprioriteit").SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
rng.EntireRow.Hidden = True
End If
End Sub

 

[Dave Peterson]

Have you thought of using data|filter|autofilter and using "not equal to"
blanks?

 

[bernd]

I've used some code which I usedin antoher file to delete empty rows.
Maybe of use for other people:

Sub hiderows3()
Dim NullRange As Range
Set NullRange = Nothing
For i = 1 To ActiveSheet.UsedRange.Rows.Count
If Range("J" & i).Value = "" Then
If NullRange Is Nothing Then
Set NullRange = (Cells(i, 1))
Else
Set NullRange = Union(NullRange, Cells(i, 1))
End If
End If
Next

If Not (NullRange Is Nothing) Then
NullRange.EntireRow.Hidden = True 'hide the rows which satisfy the
condition.
End If
End Sub