Excel - any way to auto hide empty columns when filtering rows?

I am working with data that's imported from a NOSQL database.

Sometimes I get worksheets with 1000 columns and up where most of the cells have no data in them.

I am looking for a way that when I filter the data and show only specific rows, all the columns for the visible rows which have no data at all will hide automatically.

This way, I will not have to horizontally scroll hundreds of empty columns looking for information.

If you have a solution for this need, I will be grateful.

Thanks,

Hanan Cohen

3 Answers

This code will hide columns if only the header cell is filled:

Sub KolumnHider() Dim wf As WorksheetFunction Dim i As Long, r As Range Set wf = Application.WorksheetFunction For i = 1 To 1000 Set r = Cells(1, i).EntireColumn If wf.CountA(r) < 2 Then r.Hidden = True Next i
End Sub

If there are no column headers, then make the 2 into a 1.

This VBA code will hide all blank columns whether or not they have a header.

Private Sub CommandButton1_Click() Dim rng As Range Dim nLastRow As Long Dim nLastColumn As Integer Dim i As Integer Dim HideIt As Boolean Dim j As Long Set rng = ActiveSheet.UsedRange nLastRow = rng.Rows.Count + rng.Row - 1 nLastColumn = rng.Columns.Count + rng.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

Notes:

• If you are using a standard filter command from the menu to filter
  the records, run this code first and then filter.
• If you are using VBA code to filter the records, call this subroutine first within your code. In that case, it would be better to replace "CommandButton1_Click" with some other name, perhaps "Private Sub HideBlankColumn()".

I have modified the Rajesh S code to take care of applied filters, to exit from the internal loop as soon as possible and to unhide columns if applied filters changes the visible rows. Run HydeEmptyColumns after filtering.

Public Sub HydeEmptyColumns() Dim rng As Range Dim nLastRow As Long Dim nLastColumn As Integer Dim i As Integer Dim HideIt As Boolean Dim j As Long Set rng = ActiveSheet.UsedRange nLastRow = rng.Rows.Count + rng.row - 1 nLastColumn = rng.Columns.Count + rng.Column - 1 For i = 1 To nLastColumn HideIt = True For j = 2 To nLastRow If Not Rows(j).Hidden Then If Cells(j, i).Value <> "" Then HideIt = False Exit For End If End If Next Columns(i).EntireColumn.Hidden = HideIt Next
End Sub