Hiding rows and columns based on cell value

[iamgujju]

afternoon all,

i am new and i need massive help please. please be generous as i am new.

i am trying to create spreadsheet based on cell value with dropdown list (All,a,b,c,d) - if cell A1 = ALL range a5:j20 shows everthing (neither row or column hide).

if A1="a" then row(s) and column(s) contain "a" hide them; the same for others too like if A1="b" then hide rows and columns contain "b".

i am sorry i am unable to copy/paste any pic or unable to attach anything - if you could help me your help would be much appreciated.

also please advise me whatever help i will receive can i use the same for Pivot Table too?

 

[John_w]

apologies for the confusion...
cell A1 have got drop down list....from row 5 (a5) data will start ....so if i leave column A as it is and from column B to J and all rows under those columns if matches with dropdown list....would you plz help me with that. if i leave column A (all rows) just as different name like x y z...it will not hide column A. so if you could advise me with code that would be great plz

BIB - surely you mean B5?

If the data is in B5:J20, simply change this line in my code:

    Set dataRange = Range("A5:J20")

to:

    Set dataRange = Range("B5:J20")

 

[Twollaston]

thank you very much,

so i have selected "a" from drop down and row number 7,10, 12 contain "a" - keep that rows and hide all others. the same for column 2 & 6 matches with dropdown "A" keep that unhidden and hide the rest please.

Private Sub Worksheet_Change(ByVal Target As Range)


    If Not Intersect(Target, Range("A1")) Is Nothing Then
    
        Dim arr(2 To 17, 2 To 11)
        Dim i As Long, x As Long
        
        For x = LBound(arr, 1) To UBound(arr, 1)
            For i = LBound(arr, 2) To UBound(arr, 2)
                If ActiveSheet.Range("A1") <> Cells(x, i) Then
                    Cells(x, i).EntireRow.Hidden = True
                    Cells(x, i).EntireColumn.Hidden = True
                End If
                If ActiveSheet.Range("A1") = "All" Then
                    Cells(x, i).EntireRow.Hidden = False
                    Cells(x, i).EntireColumn.Hidden = False
                End If
            Next
        Next
        For x = LBound(arr, 1) To UBound(arr, 1)
            For i = LBound(arr, 2) To UBound(arr, 2)
                If ActiveSheet.Range("A1") = Cells(x, i) Then
                    Cells(x, i).EntireRow.Hidden = False
                    Cells(x, i).EntireColumn.Hidden = False
                End If
            Next
        Next
    End If
End Sub

This is not exactly efficient but it will work

 

[iamgujju]

Hi,

i have tried this code; it does opposite what i want - i need whatever selected from drop down @ B3 - stay unhide and all others needs to be hidden.

please advise - thanks

[FONT=Verdana]Private Sub Worksheet_Change(ByVal Target As Range)[/FONT]


[FONT=Verdana]Dim dataRange As Range[/FONT]
[FONT=Verdana]Dim col As Range, row As Range[/FONT]

[FONT=Verdana]Set dataRange = Range("c6:ca1000")[/FONT]

[FONT=Verdana]If Not Intersect(Target, Range("B3")) Is Nothing And Target.Count = 1 Then[/FONT]

[FONT=Verdana]Application.ScreenUpdating = False[/FONT]

[FONT=Verdana]dataRange.Columns.Hidden = False[/FONT]
[FONT=Verdana]dataRange.Rows.Hidden = False[/FONT]

[FONT=Verdana]If Target.Value <> "ALL" Then[/FONT]
[FONT=Verdana]For Each col In dataRange.Columns[/FONT]
[FONT=Verdana]If WorksheetFunction.CountIf(col, Target.Value) > 0 Then col.Hidden = True[/FONT]
[FONT=Verdana]Next[/FONT]
[FONT=Verdana]For Each row In dataRange.Rows[/FONT]
[FONT=Verdana]If WorksheetFunction.CountIf(row, Target.Value) > 0 Then row.Hidden = True[/FONT]
[FONT=Verdana]Next[/FONT]

[FONT=Verdana]End If[/FONT]

[FONT=Verdana]Application.ScreenUpdating = True[/FONT]

[FONT=Verdana]End If[/FONT]

[FONT=Verdana]End Sub[/FONT]

]

Try this in the sheet module of the sheet containing your data. The dropdown is assumed to be a data validation list in-cell dropdown.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim dataRange As Range
    Dim col As Range, row As Range
    
    Set dataRange = Range("A5:J20")
    
    If Not Intersect(Target, Range("A1")) Is Nothing And Target.Count = 1 Then
    
        Application.ScreenUpdating = False
        
        dataRange.Columns.Hidden = False
        dataRange.Rows.Hidden = False
        
        If Target.Value <> "ALL" Then
            For Each col In dataRange.Columns
                If WorksheetFunction.CountIf(col, Target.Value) > 0 Then col.Hidden = True
            Next
            For Each row In dataRange.Rows
                If WorksheetFunction.CountIf(row, Target.Value) > 0 Then row.Hidden = True
            Next
            
        End If
        
        Application.ScreenUpdating = True
        
    End If
    
End Sub

It seems odd to me to have the dropdown in cell A1, when column A could be hidden.

 

[iamgujju]

please advise as i need to finish soon as possible please - thanks

 

[iamgujju]

please helppppppp.

 

[Twollaston]

I put a code that hides cells that match what is in the drop down and I put I code that hides everything that doesn’t match. Anyways, that was your request, so enjoy. And good luck.

 

[iamgujju]

thanks a lot for your help...

is there any advise you can provide me so i can change/amend code - which work like whatever i select only shows those data and the rest would get hidden please.

I put a code that hides cells that match what is in the drop down and I put I code that hides everything that doesn’t match. Anyways, that was your request, so enjoy. And good luck.

 

[John_w]

i have tried this code; it does opposite what i want - i need whatever selected from drop down @ B3 - stay unhide and all others needs to be hidden.

It does the opposite of what you want because that's what you originally asked for:

i am trying to create spreadsheet based on cell value with dropdown list (All,a,b,c,d) - if cell A1 = ALL range a5:j20 shows everthing (neither row or column hide).

if A1="a" then row(s) and column(s) contain "a" hide them; the same for others too like if A1="b" then hide rows and columns contain "b".

For your latest request, try this Worksheet_Change handler:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim dataRange As Range
    Dim col As Range, row As Range
    
    Set dataRange = Range("C6:CA1000")
        
    If Not Intersect(Target, Range("B3")) Is Nothing And Target.Count = 1 Then
    
        Application.ScreenUpdating = False
        
        If Target.Value = "ALL" Then
            dataRange.Columns.Hidden = False
            dataRange.Rows.Hidden = False
        Else
            For Each col In dataRange.Columns
                If WorksheetFunction.CountIf(col, Target.Value) > 0 Then
                    col.Hidden = False
                Else
                    col.Hidden = True
                End If
            Next
            For Each row In dataRange.Rows
                If WorksheetFunction.CountIf(row, Target.Value) > 0 Then
                    row.Hidden = False
                Else
                    row.Hidden = True
                End If
            Next
            
            Application.ScreenUpdating = True
  
        End If
        
    End If
    
End Sub

 

[iamgujju]

thank you so much

It does the opposite of what you want because that's what you originally asked for:

 

[dangelor]

Cross posted...https://www.excelforum.com/excel-programming-vba-macros/1280665-help-please-hide-unhide-rows-and-columns-based-on-drop-down-selection.html#post5146449