Hide columns if there are no entry's in column

B

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
 
B

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
 
B

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
 
D

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
 
B

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
 
R

Roger Govier

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

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
 
D

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
 
Top