VBA code to hide filtered columns (simple table 5 rows / 6 columns wide)

[xx3774]

Hi,

I have a simple table in Excel 2010 (5 rows / 6 columns).
I would like the user to be able to filter the table.
After the filter, I would like to hide any columns that only contain (visible) 'N' characters.

I would like to do this in VBA.

The file can be downloaded from:
https://dl.dropboxusercontent.com/u/71962278/hide_n_columns.xlsm

Any help much appreciated.
Thank you

 

[NARAYANK991]

Hi ,

Will the filter be applied only to column A ?

Narayan

 

[xx3774]

yes - only column A

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Debraj]

Hi ..

Can you please check this one too..

https://dl.dropboxusercontent.com/u/78831150/Excel/FilterEvent.xlsm

Private Sub Worksheet_Calculate()
  FilterEvent
End Sub
Sub FilterEvent()
Dim AllN As Boolean
With Range("A1").CurrentRegion
  .Columns.Hidden = False
  If ActiveSheet.FilterMode Then
  For i = 2 To .Columns.Count
  AllN = True
  For Each cell In .Columns(i).Rows.Offset(1).Resize(.Rows.Count - 1).SpecialCells(12)
  If cell <> "N" Then
  AllN = False
  Exit For
  End If
  Next cell
  If AllN Then .Columns(i).Hidden = True
  Next i
  End If
End With
End Sub

 

[Smallman]

Hi

If you put a count at the bottom of your table the code would be easier to apply.

Option Explicit
 
Sub HideIt()
    Dim rng As Range
    For Each rng In Range("B12:F12")
        If rng = 4 Then rng.EntireColumn.Hidden = Not (rng.EntireColumn.Hidden)
    Next
End Sub
 
Sub UnHideIt()
    Range("B12:F12").EntireColumn.Hidden = False
End Sub

File attached to show workings.

Take care

Smallman

 

[xx3774]

Hi ,

See if this is OK.

Narayan

Thank you for the reply

 

[xx3774]

Hi

If you put a count at the bottom of your table the code would be easier to apply.

Option Explicit

Sub HideIt()
    Dim rng As Range
    For Each rng In Range("B12:F12")
        If rng = 4 Then rng.EntireColumn.Hidden = Not (rng.EntireColumn.Hidden)
    Next
End Sub

Sub UnHideIt()
    Range("B12:F12").EntireColumn.Hidden = False
End Sub

File attached to show workings.

Take care

Smallman

Thanks for the reply

 

[xx3774]

Hi ..

Can you please check this one too..

https://dl.dropboxusercontent.com/u/78831150/Excel/FilterEvent.xlsm

Private Sub Worksheet_Calculate()
  FilterEvent
End Sub
Sub FilterEvent()
Dim AllN As Boolean
With Range("A1").CurrentRegion
  .Columns.Hidden = False
  If ActiveSheet.FilterMode Then
  For i = 2 To .Columns.Count
  AllN = True
  For Each cell In .Columns(i).Rows.Offset(1).Resize(.Rows.Count - 1).SpecialCells(12)
  If cell <> "N" Then
  AllN = False
  Exit For
  End If
  Next cell
  If AllN Then .Columns(i).Hidden = True
  Next i
  End If
End With
End Sub

this one works great - many thanks