Hide columns if there are no entry's in column

[blommerse]

Hi everyone,

I have a workbook with multiple sheets. One sheet is a overview from
all the sheets and had all dates in it.
Is there a VBA to hide columns when there are no entry's in it?
The code has to work when I open the sheet "overview"
Hope someone can help me with it!

Thanks in advanced!
Regards Berry

 

[Bernie Deitrick]

Berry,

If you have a row that when blank would indicate which columns to hide, you could use

On Error Resume Next
Rows("1:1").SpecialCells(xlCellTypeBlanks).EntireColumn.Hidden = True

HTH,
Bernie
MS Excel MVP

 

[blommerse]

Thanx Bernie,
It is almost working.
The only thing is the cells have a fomule in it. So excel doesn't see
them blanc.
Is there an other way??
Berry

 

[Dave Peterson]

Can you look at the first row of the .UsedRange?

If yes:

Option Explicit
Sub testme()
Dim myCell As Range
With ActiveSheet
For Each myCell In .UsedRange.Rows(1).Cells
myCell.EntireColumn.Hidden = CBool(myCell.Value = "")
Next myCell
End With
End Sub

 

[blommerse]

It is working now, but it very, very slow. At least it takes 10
seconds to check the whole file.
After column DA there is nothing in...
Hope you can help me make it faster.
This is the code I now use:

Private Sub Worksheet_Activate()
Dim myCell As Range
With ActiveSheet
For Each myCell In .UsedRange.Rows(2).Cells
myCell.EntireColumn.Hidden = CBool(myCell.Value = "")
Next myCell
End With
End Sub

Thanks

 

[Roger Govier]

Hi

After column DA there is nothing in...
Hope you can help me make it faster

Dave's code is using the Used Range so it will not be going beyond
column DA if that is the last column.
Maybe you have some formulae with volatile functions, which will be
causing a lot of re-calculation.
You can turn off calculation (and screen updating) at the beginning, and
back on at the end. That will probably speed things up.

Private Sub Worksheet_Activate()
Dim myCell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With ActiveSheet
For Each myCell In .UsedRange.Rows(2).Cells
myCell.EntireColumn.Hidden = CBool(myCell.Value = "")
Next myCell
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[Dave Peterson]

Just to add to Roger's response...

If you can see the pagebreak dotted lines, then excel will slow down.
If you're in View|Page break preview mode, then excel will slow down.

So combining these with Roger's .screenupdating and .calculationmode changes:

Option Explicit
Sub testme()
Dim myCell As Range
Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
For Each myCell In .UsedRange.Rows(1).Cells
myCell.EntireColumn.Hidden = CBool(myCell.Value = "")
Next myCell
End With

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode
Application.ScreenUpdating = True

End Sub