Hide rows and columns

[Macklemore]

Hi,

I am very new to VBA. Yes I know it's 2013 but I'm seeing the value in it for the first time. Everything I did prior to this was child's play, not that this is complex to most of you all.

I'm looking to see if there is a way to hide rows and columns via VBA in excel.

Cell b1 contains a company name from a pick list
Cell c1 contains an ever changing column name

Row A2:AM2 contains column names
Row B3:B70002 contains the names of 12 companies

Cell AE2:AM2 contain column names. One of these column names will be displayed in Cell C1

So what I need is;

1. Hide all rows that are not equal to the values in cell B1
2. Hide all columns in the AE:AM range whose AE2:AM2 cells do not equal the value in cell C1
3. All columns AE:AM should hide/unhide if the values in C1.
4. All rows A3:AM70002 should hide/unhide if the values in B1 change.

Any help would be much appreciated.

 

[Macklemore]

I've searched through the forums and modified a piece of code that work well for hiding rows. I need to now incorporate how to hide columns.

here's my row hiding hack.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ActiveSheet.AutoFilterMode = False
Range("A2:AM7000").AutoFilter
Range("A2:AM7000").AutoFilter Field:=2, Criteria1:=Range("B1").Text

Application.ScreenUpdating = True


End Sub

 

[Macklemore]

[Solved] Hide rows and columns

Sorry seems with a little digging I was able to hack more at it. Peter_SSs showed me the way in post http://www.mrexcel.com/forum/excel-questions/687674-show-columns-based-cell-value.html #6

My final code is;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ActiveSheet.AutoFilterMode = False
Range("A2:AM7000").AutoFilter
Range("A2:AM7000").AutoFilter Field:=2, Criteria1:=Range("B1").Text


Dim cel As Range, Headers As Range
Dim s As String

Set Headers = Range("AE2:AM2")
If Target.Address = "$C$1" Then
s = Target.Value
Application.ScreenUpdating = False
If s = "" Then
Headers.EntireColumn.Hidden = False
Else
For Each cel In Headers
cel.EntireColumn.Hidden = Not cel.Value = s
Next cel
End If
Application.ScreenUpdating = True
End If

End Sub