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

 

[Andrew Poulsom]

What range does your data actually occupy - H4:AX5000?

 

[BSAGAERT]

H4:AX650 actually

 

[Andrew Poulsom]

Try this in the module for the worksheet:

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("H4:AX650")
        .EntireColumn.Hidden = False
        .EntireRow.Hidden = False
        For i = 2 To .Rows.Count
            With .Cells(i, 1)
                .EntireRow.Hidden = .Value <> Target.Value
            End With
        Next i
        For i = 2 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]

Thanks for the fast response! However it is not working. On every selection, it hides the same rows and columns and does not change upon change in $A$1.

 

[Andrew Poulsom]

It worked when I tried it. Let's see some sample data and what you are putting in A1.

 

[BSAGAERT]

is there a way to laod the excel?

 

[Andrew Poulsom]

Just copy a small but representative sample of your data and paste it into a reply. And give an example of what you are putting in A1.

 

[BSAGAERT]

Management

Management

Management

Management

Management

Management

Management

Management

Management

Management

Medewerker 1

Medewerker 2

Medewerker 3

Medewerker 4

Medewerker 5

Afdeling

Taak

Opleiding

Opleiding nummer

Type opleiding

Frequentie

Actieve versie

Versie datum

Status/ versie

Datum

Status/ versie

Datum

Status/ versie

Datum

Status/ versie

Datum

Status/ versie

Management

Documentbeheer

beheer van agreements en contracten

101x

x

x

x

x

x

Management

Kwaliteit

qualification & approval of suppliers

1040

4

17/06/2013

x

x

x

x

x

Management

Kwaliteit

uitvoeren van een uitgebreid onderzoek

1050

x

x

x

x

x

Management

Kwaliteit

beheer van CAPA's

1051

x

x

x

x

x

Management

Kwaliteit

Quality risk management

1060

1

25/04/2013

x

x

x

x

x

Management

Kwaliteit

Facility tour

1090

1

28/05/2013

x

x

x

x

x

Management

Kwaliteit

Terugroepen van goederen van de markt

1110

3

12/08/2013

x

x

x

x

x

Management

Kwaliteit

Management review

1140

1

7/08/2013

x

x

x

x

x

Management

Kwaliteit

Interne audit

1150

5

2/08/2013

x

x

x

x

x

Management

Kwaliteit

Externe kwaliteitsaudits

1151

4

7/08/2013

x

x

x

x

x

Management

Kwaliteit

Tekortkoming onderzoek

1160

x

x

x

x

x

Management

Kwaliteit

APR

24/03/2016

x

x

x

x

x

Management

Kwaliteit

aankoop van producten

6000

x

x

x

x

x

Management

Kwaliteit

beheer van toestellen

5030

3

13/08/2012

x

x

x

x

x

<colgroup><col><col><col><col><col><col><col><col><col><col><col span="7"></colgroup><tbody>
</tbody>

 

[Andrew Poulsom]

I thought you said your data started in column H row 4. What range does your sample data occupy?