Prev: Not at all clear on use of variables and/or object variables
Next: personalised rota
From: David Hainsworth David on 4 Jul 2008 12:16
I have a large spreadsheet which will be populated based on the selection
from a drop down list box. The values populated will contain alot of zero
values which I have blanked out using the tools/options functionality but I
would like to automatically hide the column if all values within a range in
that column are zero. Can this be done using advanced filter ? if so how ...
any other suggestions ?
From: JMay on 4 Jul 2008 14:31
I use the following Macro in a Workbook of mine.
Before running the Macro (Which is in a Standard Code module)
Click and sellect the Column-Header (First to Last) of your data;
Allows for say Column C thru Column S (Columns A & B Blank)

Try this on a COPY of your WB first

Sub HideColumnsWithZeroValues()
Dim i As Integer
Dim ColCount As Integer
Application.ScreenUpdating = False
StrCol = Selection(1).Column
ColCount = Selection.Columns.Count + StrCol - 1
With Selection
For i = StrCol To ColCount
If Application.WorksheetFunction.Sum(Columns(i)) = 0 Then
Columns(i).EntireColumn.Hidden = True
End If
Next i
End With
Application.ScreenUpdating = True
End Sub

Good Luck

"David Hainsworth" wrote:

> I have a large spreadsheet which will be populated based on the selection
> from a drop down list box. The values populated will contain alot of zero
> values which I have blanked out using the tools/options functionality but I
> would like to automatically hide the column if all values within a range in
> that column are zero. Can this be done using advanced filter ? if so how ...
> any other suggestions ?
 | 
Pages: 1
Prev: Not at all clear on use of variables and/or object variables
Next: personalised rota