Hiding rows and columns based on cell value

iamgujju

New Member
Joined
Aug 30, 2015
Messages
29
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?
 
Last edited:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.

Code:
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.
 
Upvote 0
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

Code:
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
 
Upvote 0
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
 
Upvote 0
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)
 
Last edited:
Upvote 0
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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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