Clunky Slow Code, Hides Empty Rows, Takes too long...

Hello, I've written code that hides rows on different sheets if they are empty and in one case changes column width to keep the formatting looking ok. I've used non-contiguous named ranges because there are multiple places on each row that could (or could not) be empty. It does exactly what I want, but it takes a long time to process and will only get longer if I continue to add new sheets. I'm pretty new to VBA, but I know I didn't write it well as I am iterating through multiple ranges and performing tests on each cell. I'm pretty sure I could use an array, but I failed at coding that myself. Moreover, using xlspecialcellblanks isn't working, because the "empty" cells have formulas in them. Any help would be greatly appreciated! Thanks very much.

Sub F2F4HideRows()

Application.ScreenUpdating = False

Dim rng As Range

Sheets("F2").Range("F2HideRows").Rows.EntireRow.Hidden = True
Sheets("F4").Range("F4HideRows").Rows.EntireRow.Hidden = True
Sheets("F5").Range("F5HideRows").Rows.EntireRow.Hidden = True
Sheets("F6").Range("F6HideRows").Rows.EntireRow.Hidden = True
Sheets("F4").Columns("C").ColumnWidth = 17
Sheets("F4").Columns("D").ColumnWidth = 17
Sheets("F4").Columns("F").ColumnWidth = 17

On Error Resume Next

For Each rng In Sheets("F2").Range("F2HideRows")

    If rng <> Empty Then
    
    rng.Rows.EntireRow.Hidden = False
    
    End If
    
Next

For Each rng In Sheets("F4").Range("F4HideRows")

If rng <> Empty Then
    
    rng.Rows.EntireRow.Hidden = False
    Sheets("F4").Columns("C").ColumnWidth = Sheets("F4").Columns("C").ColumnWidth + 0.3
    Sheets("F4").Columns("D").ColumnWidth = Sheets("F4").Columns("D").ColumnWidth + 0.3
    Sheets("F4").Columns("F").ColumnWidth = Sheets("F4").Columns("F").ColumnWidth + 0.3
    
    End If
    
Next

For Each rng In Sheets("F5").Range("F5HideRows")

    If rng <> Empty Then
    
    rng.Rows.EntireRow.Hidden = False
    
    End If
    
Next

For Each rng In Sheets("F6").Range("F6HideRows")

    If rng <> Empty Then
    
    rng.Rows.EntireRow.Hidden = False
    
    End If
    
Next

On Error GoTo 0

Application.ScreenUpdating = True

End Sub

Oh well, thanks for trying anyways. I was thinking maybe there was an array solution, so I could reference the array index instead of each cell but I don't know how to do it. I know I could get the range to be hidden set dynamically to a variable but then I couldn't get the column width part to work too.

Brilliant Question by the way. It got me thinking.

This will un-hide rows that contain numbers or text, whether they are constants or returned by a formula.

No loops are involved.

Enjoy.

Hi mehmetcik,

Thanks so much for your reply! Unfortunately, I want to hide rows that have formulas (or references) but are returning blanks due to no input in the reference cell. Your code unhides these which are a reference to empty cells. I tried IF(ref="","",ref) in the cells as well and still no dice. I think that is why the "Empty" property has worked in my code. I think I may have to set the area to be hidden first by a loop and then hide that. I'm really new with arrays, and I got one that seems to iterate through an index of cells, but for some reason (even though I can see it has the correct reference in debug) it doesn't recognize the cell... maybe I need to redim the array, but again, I am inexperienced with them.

Hi

try this code.

Sub UnhideMacro()

ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, xlNumbers + xlTextValues).Select
Selection.EntireRow.Hidden = False
ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlNumbers + xlTextValues).Select
Selection.EntireRow.Hidden = False
End Sub

Hi

All you have to do is to hide the formulas returning blanks I would have thought.