unhide (or hide) row based on a filter value

Good morning everyone,

I have an issue i cannot solve for my excel file.. i explain

I received a file from a company where there is a pivot table with a filter (anyway this is what i think)...

I would like, based on the value choosen with the filter, to show only the correspondant product linked to this number.. I have insterted a screenshot explaning what i need
Excel issue.png
Would you have a solution for me ? (VBA or something else)

Thanks for your support

Guismo30

Can you provide a sample file, with dummy data, refer to the yellow banner above. VBA can be manipulated to hide rows that don't match the filter.

Hi
thanks for your answer, please find attached the file
Pierre

In this iteration i tied updating c4 with a worksheet_change event so that you don't have to select the turn on button . You'll still need to use the filter off to clear the autofilter. Here is the worksheet change code :

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim last As Long
Dim sht As String
  
    Application.EnableEvents = True
    If Not Intersect(Target, Range("C4")) Is Nothing Then
        'specify sheet name in which the data is stored
        sht = "Commercial attribs per Brick"
        'Find the LastRow in the sheet at bottom of lowest column ie count col
        last = Sheets(sht).Cells(Rows.Count, "B").End(xlUp).Row
        Set Rng = Sheets(sht).Range("A9:CS" & last)
        'set autofilter
            With Rng
                .AutoFilter
                .AutoFilter Field:=2, Criteria1:=Target.Value2(1, 2)
            End With
   
    End If

End Sub

Here is the filter on and off code:

Option Explicit

Sub filter()

Application.ScreenUpdating = False
Dim x As Range
Dim Rng, T As Range
Dim last As Long
Dim sht As String

'specify sheet name in which the data is stored
sht = "Commercial attribs per Brick"
'change filter column in the following code
last = Sheets(sht).Cells(Rows.Count, "B").End(xlUp).Row
Set Rng = Sheets(sht).Range("A9:CS" & last)
    For Each x In Range("C4")
        With Rng
            .AutoFilter
            .AutoFilter Field:=2, Criteria1:=x.Value
        End With
    Next x
    With Application
    .CutCopyMode = False
    .ScreenUpdating = True
    End With

End Sub

Sub TurnOffFilter()
Dim sht As String

'specify sheet name in which the data is stored
    sht = "Commercial attribs per Brick"
    ' Turn off filter
    Sheets(sht).AutoFilterMode = False
End Sub

I hope this is the final iteration. a change event on C4 now hides all other columns. A button exist to reverse the hiding of rows. Maybe this is what you envisioned in the first place. Here is the code and the working file is attached.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng, Crng, c As Range
Dim sht As String
Dim last As Long
    Application.EnableEvents = True
    If Not Intersect(Target, Range("C4")) Is Nothing Then
        'specify sheet name in which the data is stored
        sht = "Commercial attribs per Brick"
        'Find the LastRow in the sheet at bottom of lowest column ie count col
        last = Sheets(sht).Cells(Rows.Count, "B").End(xlUp).Row
        Set Rng = Sheets(sht).Range("A9:CS" & last)
        Set Crng = Sheets(sht).Range("B10:B" & last)
        'set autofilter
        For Each c In Crng
            If c = Target.Value2(1, 2) Then
                c.EntireRow.Hidden = False
            Else
                c.EntireRow.Hidden = True
            End If
            'With Rng
                '.AutoFilter
                '.AutoFilter Field:=2, Criteria1:=Target.Value2(1, 2)
            'End With
        Next c
    End If

End Sub

If the intention is to show only the Brick numbers that correspond to the value under Filter, then it's better to create a duplicate Brick column in the database and name the column header, say Brick2. Bring Brick2 to the Filters section with Brick remaining in the Rows section of the PivotTable.

Hi Josephteh
not exactly what i wanted but thanks a lot
Pierre