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

B

BSAGAERT

New Member
Joined
Sep 26, 2013
Messages
10
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
 
Last edited:

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Try this in the module for the worksheet:

Code: 
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
[TABLE="width: 1790"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col span="7"></colgroup><tbody>[TR]
[TD]Management[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Management[/TD]
[TD]Management[/TD]
[TD]Management[/TD]
[TD]Management[/TD]
[TD]Management[/TD]
[TD]Management[/TD]
[TD]Management[/TD]
[TD]Management[/TD]
[TD]Management[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]Medewerker 1[/TD]
[TD][/TD]
[TD="align: right"]Medewerker 2[/TD]
[TD][/TD]
[TD="align: right"]Medewerker 3[/TD]
[TD][/TD]
[TD="align: right"]Medewerker 4[/TD]
[TD][/TD]
[TD="align: right"]Medewerker 5[/TD]
[/TR]
[TR]
[TD]Afdeling[/TD]
[TD]Taak[/TD]
[TD]Opleiding[/TD]
[TD]Opleiding nummer[/TD]
[TD]Type opleiding[/TD]
[TD]Frequentie[/TD]
[TD]Actieve versie[/TD]
[TD]Versie datum[/TD]
[TD]Status/ versie[/TD]
[TD]Datum[/TD]
[TD]Status/ versie[/TD]
[TD]Datum[/TD]
[TD]Status/ versie[/TD]
[TD]Datum[/TD]
[TD]Status/ versie[/TD]
[TD]Datum[/TD]
[TD]Status/ versie[/TD]
[/TR]
[TR]
[TD]Management[/TD]
[TD]Documentbeheer[/TD]
[TD]beheer van agreements en contracten[/TD]
[TD]101x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Management[/TD]
[TD]Kwaliteit[/TD]
[TD]qualification & approval of suppliers[/TD]
[TD]1040[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]17/06/2013[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Management[/TD]
[TD]Kwaliteit[/TD]
[TD]uitvoeren van een uitgebreid onderzoek[/TD]
[TD]1050[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Management[/TD]
[TD]Kwaliteit[/TD]
[TD]beheer van CAPA's[/TD]
[TD]1051[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Management[/TD]
[TD]Kwaliteit[/TD]
[TD]Quality risk management[/TD]
[TD]1060[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]25/04/2013[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Management[/TD]
[TD]Kwaliteit[/TD]
[TD]Facility tour[/TD]
[TD]1090[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]28/05/2013[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Management[/TD]
[TD]Kwaliteit[/TD]
[TD]Terugroepen van goederen van de markt[/TD]
[TD]1110[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]12/08/2013[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Management[/TD]
[TD]Kwaliteit[/TD]
[TD]Management review[/TD]
[TD]1140[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]7/08/2013[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Management[/TD]
[TD]Kwaliteit[/TD]
[TD]Interne audit[/TD]
[TD]1150[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]2/08/2013[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Management[/TD]
[TD]Kwaliteit[/TD]
[TD]Externe kwaliteitsaudits[/TD]
[TD]1151[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]7/08/2013[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Management[/TD]
[TD]Kwaliteit[/TD]
[TD]Tekortkoming onderzoek[/TD]
[TD]1160[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Management[/TD]
[TD]Kwaliteit[/TD]
[TD]APR[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]24/03/2016[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Management[/TD]
[TD]Kwaliteit[/TD]
[TD]aankoop van producten[/TD]
[TD]6000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Management[/TD]
[TD]Kwaliteit[/TD]
[TD]beheer van toestellen[/TD]
[TD]5030[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]13/08/2012[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
418
rahildhody
R
J
  • Solved
VBA to compare rows in two different sheets, if match highlight cell
2
Replies
11
Views
2K
DaveB673
DaveB673
T
  • Solved
Help with VBA Code in Excel
Replies
2
Views
305
tixarah
T
E
  • Solved
Change format of cell based on value of cell in corresponding row?
2
Replies
12
Views
486
EssKayKay
E
X
  • Question Question
Excel VBA - Return row value & Colum header value
Replies
0
Views
325
Xenon72
X

Forum statistics

Threads
1,223,790
Messages
6,174,594
Members
452,574
Latest member
hang_and_bang

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top