Hide Blank Rows & Those With "" Empty Text

jamesparker_1 · May 21st 2007

Hi All

I have a macro that hides a row if a cell in that row is blank. The VBA code is shown below.

Code

With Edin_Cap
        .Range("B26:B36").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
        .Range("B44:B86").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
        .Range("B93:B124").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
    End With

I have discovered that this formula becomes a problem where a cell might contain a formula but no value. For example, if the cell contains the formula:

=IF(ISBLANK('Template'!B78),"",'Template'!B78)

if the above value is "" then the 'hide entire row' macro doesnt work. How can I hide a row if the cell VALUE is blank?

Thanks

James

Mavyak · May 21st 2007

Re: Hide Blank Rows & Those With "" Empty Text

Try the Len() function

Code

If Len(Range("A1").value) = 0 Then
     Range("A1").EntireRow.Hidden = True
End If

You will probably have to put that in a function that accepts a range variable and cycle through the cells in reverse order.

Hide Blank Rows & Those With "" Empty Text

Participate now!

Share