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?

 

[iamgujju]

Please help as I am desperate

 

[Twollaston]

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".

rows and columns a5:j20?

which rows and columns do you want to hide

 

[John_w]

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.

 

[Twollaston]

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

Yes quite odd.
I'm also guessing he would want to keep cells with "a" hidden after he changes A1 from "a" to "b" due to the "All" selection to clear all the hidden rows. But not really sure without clarification.
If that is the case he could use something like

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("A1")) Is Nothing Then


    Dim arr(5 To 20, 1 To 7)
    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
End If
End Sub

 

[iamgujju]

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

 

[Twollaston]

Dim arr(5 To 20, 1 To 7)

You can amend the above portion of the code to have it look at whatever cells you want.
5 to 20 means Rows 5-20 and then 1-7 means columns 1-7 (columns A to G)

For B-J would it would be 2-10 and then pick whatever rows you want

So something like Dim arr(5 To 20, 2 To 10)

 

[iamgujju]

thank you so much....i will try tomorrow and will let you know. really appreciate it.

 

[iamgujju]

thank you very much,

i have tried and its hiding everything - i know i may have confused everyone here - please accept an apology.

what i am trying to do is - if i select value from drop down and if that matches row data first - then keep those rows as it is and hide all others.

and the same for columns.

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.

Yes quite odd.
I'm also guessing he would want to keep cells with "a" hidden after he changes A1 from "a" to "b" due to the "All" selection to clear all the hidden rows. But not really sure without clarification.
If that is the case he could use something like

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("A1")) Is Nothing Then


    Dim arr(5 To 20, 1 To 7)
    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
End If
End Sub

 

[iamgujju]

your help would be much appreciated.