Macro to hide all columns without anything in then from columnA to Bz

[tonywatsonhelp]

Hi Everyone,
I need a macro to hide all columns and all rows that do not contain any values within this range (A1:Bz200)

i thought it would be easy but get stuck

Tony

 

[mumps]

Try:

Sub hideRowsCols()
    Range("A1:A200").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
    Range("A1:BZ1").SpecialCells(xlCellTypeBlanks).EntireColumn.Hidden = True
End Sub

 

[tonywatsonhelp]

Special cells, Knew i'd missed something, that's twice in one day you have saved me thank you so much

Tony

 

[tonywatsonhelp]

Oh dear I know I had over simplified my request so sorry
what I meant was that row 1 has formulas in it and I want to hide the columns that formulas =""
sorry but any ideas?

Thanks

Tony

 

[mumps]

Will all the blank rows also have formulas in them that return "" or just row 1?

 

[My Aswer Is This]

Try this:

Sub Check_For_Formulas()
'Modified 3-5-18 11:55 AM EST
Application.ScreenUpdating = False
Dim LastColumn As Long
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Dim cell As Range
    For Each cell In Range(Cells(1, LastColumn), Cells(1, 1))
Select Case True
Case cell.HasFormula
If cell.Value = "" Then
Columns(cell.Column).Hidden = True
Else
Columns(cell.Column).Hidden = False
End If
End Select
Next
Application.ScreenUpdating = True
End Sub

 

[tonywatsonhelp]

Thank you and the answer is
this is great
Thanks
Tony

 

[My Aswer Is This]

Thank you and the answer is
this is great
Thanks
Tony

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.