Help with Filtering

[yus786]

I would like to filter one of my columns but would like to select the filtering from another cell.

Have a look at this:

Instead of having to click on the arrow below CLASS - i want to be able to have something similar (or a combo box type thing) where it says CLASS at the top row.

How can this be achieved?

Thanks

 

[Brianwarnock]

You can write code to hide the rows that where a cell does not match a given value.

Brian

 

[yus786]

You can write code to hide the rows that where a cell does not match a given value.

Brian

thanks for the advise but i'm bobbins at writing codes

 

[Brianwarnock]

Its getting a bit late here for me to look at a combo but if you had a cell named Filtercell in which you typed your selection then

Public Sub FilterClass()
' BJWARNOCK
Dim myrange As Range
Dim c As Range

Worksheets("sheet1").Activate
Set myrange = Range("$b$2", Range("b65536").End(xlUp))
      
   For Each c In myrange
    If c <> Range(Names("filtercell")) Then
       c.EntireRow.Hidden = True
    End If
   Next c

End Sub

Placed in a module will work.

I'll look back in tomorrow.

Brian

 

[Brianwarnock]

The use of Range(Names etc only appears to work in Module code and a command button only appears to be capable of running worksheet code so change the code to

Dim myrange As Range
Dim c As Range

Worksheets("sheet1").Activate
Set myrange = Range("$b$2", Range("b65536").End(xlUp))
      
   For Each c In myrange
    If c <> Range("G1") Then   ' G1 is where the class required is entered
' obviously you should change this to your requirements
       c.EntireRow.Hidden = True
    End If
   Next c

but first add a command button to your sheet and then right click it , view code from local menu and copy and paste your code into the command buttons Sub.

Brian

 

[Brianwarnock]

Ok after rereading your first post have changed my mind, an old guy's perogative.

Open your worksheet select Alt F11 to open the VB editor and paste in the code below
Alter the ranges as required $H$1 is the cell you are going to enter the Class

B2:b6etc is the col and starting cell of the data.

Now everytime you enter anything in the Class selection and hit enter the filter will apply.


Brian

Oh the code!

Private Sub Worksheet_Change(ByVal target As Range)

If target.Address <> "$H$1" Then Exit Sub
Range("a1", "A65536").EntireRow.Hidden = False


' BJWARNOCK
Dim myrange As Range
Dim c As Range

Set myrange = Range("$b$2", Range("b65536").End(xlUp))
      
   For Each c In myrange
    'If c <> Range(Names("filtercell")) Then
    If c <> Range("h1") Then
       c.EntireRow.Hidden = True
    End If
   Next c
End Sub