Hide Rows with "" value?

[Brigg]

Column A has formulas that pull information from other tabs. So that it isn't cluttered up, I have it returning "" if the formula is FALSE. However, it does not seem to be recognizing the "" value. Is it even possible to do or should I be trying something else? Any help is much appreciated.

Thx!

Sub PostCondense()
Dim LastRow As Long
 Dim Rng As Range
 LastRow = Range("A38").End(xlUp).Row '
Set Rng = Range("A8:A" & LastRow) '
Application.ScreenUpdating = False
 For Each Cell In Rng
If Cell.Value = "" Then
Cell.EntireRow.Hidden = True
End If
Next Cell
Application.ScreenUpdating = True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

 

[Andrew Poulsom]

What's your formula that returns ""? By the way AutoFilter would be quicker than a loop.

 

[Brigg]

This is what's in one of the cell's:

=IF(Schedule!A28>0,Schedule!A28,"")

 

[Andrew Poulsom]

Your code works for me with that formula.

 

[Brigg]

ADVERTISEMENT

What else would cause this macro to not function? If I delete the formula and run the macro, it works. But it doesn't work when calculated this particular value. Why am I doing wrong?

 

[Andrew Poulsom]

Maybe Schedule!A28 contains a space, in which case that's what your formula will return. Does this formula work fo you?

=IF(ISNUMBER(Schedule!A28),Schedule!A28,"")

 

[Brigg]

Column A from the Schedule worksheet is a list of names. Would this be the cause?

 

[Jonmo1]

Perhaps this line isn't getting the correct last row #
LastRow = Range("A38").End(xlUp).Row

If column A is formulas, how far down are those formulas going?
If it's going beyond A38, then your lastrow variable will be 1, making rng A1:A8
This would explain why removing the formula resolves the issue.


Try this instead
LastRow = Cells(Rows.Count,"A").End(xlUp).Row