Can I automatically hide a column with zero values in Excel

  • Thread starter David Hainsworth
  • Start date

D

David Hainsworth

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 ?
 
Ad

Advertisements

J

JMay

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top