Macro Help

[volkl77]

Hi All. Is it possible to set up a macro to hide all the blank rows in a given area on all the tabs of a worksheet? If so any help would be greatly appreciated. Thanks!!

 

[Smitty]

You can record a macro doing Ctrl+G (Goto SPecial)-->Blanks-->Hide rows.

You could also try using a Data Filter and uncheck blanks from the list.

HTH,

 

[volkl77]

This works but will only allow me to do one sheet at a time.

Since all sheets are the exact same layout is it possible to set a range of rows (19 to 168) and if column "e" is blank it hides or deletes it?

 

[Smitty]

You can use a For...Each...Next construct:

Sub Foo()
Dim ws as worksheet
  For Each ws in Activeworkbook.Worksheets
     '  Your code here
  Next ws
end Sub

 

[volkl77]

i am testing this code to hide rows, but i keep getting an error saying "type mismatch", at the bolded text any ideas?

Sub Hide_Blanks()
'
' Hide_Blanks Macro
'

'
If Range("E19:E168").Value = "0" Then
Range("E19:E168").EntireRow.Hidden = True
End If

End Sub

 

[Smitty]

Well, you can't exactly evaluate a range that way, it's too broad a stoke to actually work.

If you want to check each cell in a column for a 0 value you'd have to loop (and lose the quotes around the 0), although Data Filter would be much easier.