How do I automatically hide empty columns in Excel?

[Ned Mains]

I have a spreadsheet which is prepared each month with approximately 167
columns, importing data from our application's database. Each month, a large
number of the columns of the spreadsheet contain no data, so other than the
column heading row, there is no data in the column. However, all columns are
necessary, as there may be data for any given month.

Is there a way to set up the spreadsheet to automatically hide the columns
that contain NO data, so only those columns containing data appear (making
the spreadsheet more user friendly)?

 

[ward376]

You can use this code. It assumes that the header is in the first row,
the data starts in the second row and that you want all columns
evaluated.

'Gary's Student
Sub way()
Dim r As Range
Dim nLastRow As Long
Dim nLastColumn As Integer
Dim i As Integer
Dim HideIt As Boolean
Dim j As Long

Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
nLastColumn = r.Columns.Count + r.Column - 1

For i = 1 To nLastColumn
HideIt = True
For j = 2 To nLastRow
If Cells(j, i).Value <> "" Then
HideIt = False
End If
Next
If HideIt = True Then
Columns(i).EntireColumn.Hidden = True
End If
Next

End Sub

Cliff Edwards

 

[Don]

There are two other options

1) Using the Custom view option under view. You can setup a vew that is
user friendly but may hide a column that has data.

2) Using a macro to hide columns. You can do tools/macro/record new macro
and hid the cells you do not want the tools/macro/stop . The next time you
load , you can run the macro and the columns will hide