Autohide rows and colums based on cell value and column and row header

[BSAGAERT]

I have a problem wich is similar to some already posted, but yet different and more complicated.

I want to limit the number of rows and columns based on cell content in the first row and the first column.
What I mean is, based on the value X in A1, I want to see the columns that have X in the first row of a the range "H:AX", and the rows that have X in the first column in the range "5:2000"
If no selection is made, I want to see all rows and all columns.
Is there a <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VbA</acronym> solution for this case as well?
Excel 2013
Windows 8
Reposted under http://www.mrexcel.com/forum/excel-...olums-based-cell-value-column-row-header.html since similar, yet different

 

[BSAGAERT]

I changed the range to cover A4:BB650 which is the full range (sample data is A1:BB17) and the rows are functionning correctly now. As you can see from sample data, the columns only have relevant data for hide/unhide as from column I.

 

[BSAGAERT]

I changed you set up to cover the following range A4:BB650, which is the actual range. The rows are filtering correctly now, however the columns all disappear. The sample data is A1:017

 

[Andrew Poulsom]

It looks like your column headings are in row 1, starting in column P, and your row headings start in row 1. In that case try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    If Target.Address <> "$A$1" Then Exit Sub
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    With Range("H1:AX650")
        .EntireColumn.Hidden = False
        .EntireRow.Hidden = False
        For i = 4 To .Rows.Count
            With .Cells(i, 1)
                .EntireRow.Hidden = .Value <> Target.Value
            End With
        Next i
        For i = 9 To .Columns.Count
            With .Cells(1, i)
                .EntireColumn.Hidden = .Value <> Target.Value
            End With
        Next i
    End With
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

 

[BSAGAERT]

Super!! After a slight modification, I managed to make it work. This one did it:

Private Sub Worksheet_Change(ByVal Target As Range)    Dim i As Long
    If Target.Address <> "$A$1" Then Exit Sub
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    With Range("A1:BB650")
        .EntireColumn.Hidden = False
        .EntireRow.Hidden = False
        For i = 4 To .Rows.Count
            With .Cells(i, 1)
                .EntireRow.Hidden = .Value <> Target.Value
            End With
        Next i
        For i = 9 To .Columns.Count
            With .Cells(1, i)
                .EntireColumn.Hidden = .Value <> Target.Value
            End With
        Next i
    End With
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

THanks a lot for your help, and most certainly the speed of it!