VBA to hide rows based on formula blank...

[Matty]

Hi,

I'm looking for a piece of code for hiding a non-contiguous set of rows in a Worksheet based on the value in column B being a formula blank (i.e. ="")

At the moment I have this, but it makes my Workbook grind to a halt:

Sub HideBlankRows()
    Application.ScreenUpdating = False
    
    With Sheet8
        .Calculate
        For Each c In .Range("B7:B156, B165:B239, B244:B318, B323:B337, B342:B356")
            c.EntireRow.Hidden = c.Value = ""
        Next c
    End With
    
    Application.ScreenUpdating = True
    
End Sub

I've seen autofilter used before, but the problem is that my range isn't contiguous.

Anyone got any bright ideas?

Thanks,

Matty

 

[alansidman]

Try this slight modification

Option Explicit


Sub HideBlankRows()
Dim c As Range
    Application.ScreenUpdating = False
    
    With Sheet8
            For Each c In .Range("B7:B156, B165:B239, B244:B318, B323:B337, B342:B356")
            If c.Value = "" Then
            c.EntireRow.Hidden = True
            End If
        Next c
    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[Matty]

Thanks Alan, but it's made little difference.

I think the problem is due to the fact that there are some fairly heavy formulas on the Worksheet and Excel is, seemingly, recalculating each time a row is hidden (even though calculation is set to Manual).

Cheers,

Matty

 

[Matty]

Bump...

 

[vds1]

Just a thought.. How about color those entire row yellow and filter by color and hide them..

 

[Matty]

Hi,

I've gone with the following, which seems to perform very well:

Sub HideBlankRows()

    Application.ScreenUpdating = False
    
    With Sheet8
        .Calculate
        With .Range("B7:B156, B165:B239, B244:B318, B323:B337, B342:B356")
            .EntireRow.Hidden = False
            .SpecialCells(xlCellTypeFormulas, 2).EntireRow.Hidden = True
        End With
    End With
    
    Application.ScreenUpdating = True
    
End Sub

Need to test it on a larger data set tomorrow at work, but I'm confident.

Cheers for everyone's input.

Matty