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

Yes, there is a way to automatically hide empty columns in Excel. Here are the steps:

1. Select the entire range of columns that you want to check for empty columns. You can do this by clicking on the first column heading and dragging across to the last column heading.
2. Click on the "Home" tab in the ribbon at the top of the Excel window.
3. Click on the "Find & Select" button in the "Editing" section of the ribbon.
4. Click on "Go To Special" in the drop-down menu.
5. In the "Go To Special" dialog box, select "Blanks" and click "OK". This will select all of the empty cells in the selected range.
6. Right-click on any of the selected cells and click "Hide". This will hide all of the columns that contain empty cells.
7. To unhide any hidden columns, select the columns on either side of the hidden columns, right-click, and click "Unhide".
8. Save your spreadsheet.

Now, whenever you open the spreadsheet, any columns that contain no data will be automatically hidden, making the spreadsheet more user-friendly.

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

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

"Ned Mains" wrote:

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