filter row on value from another sheet in the same workbook

[VbaHell]

Hello all

I hope you can help on this one

Is it possible to filter your data based on a value from another worksheet in the same workbook.
I want to filter on any rows not equal to "<> INST", This is the code I have am playing with but it's not working, I can get it to work if I hard code it to "<>INST" but I wanted the flexibility of change the value in sheet5 cell "C2"

With Sheets("Data")
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("N:N").AutoFilter 1, "<>" & Sheet5.Range("C2")
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With

 

[Fluff]

As long as C2 contains INST then that code should work.
Check C2 for any leading/trailing spaces, also check that you are looking at the correct sheet, ie sheet codename "Sheet5" rather than sheet name

 

[VbaHell]

Hi Fluff

I can see that the filter is working now but I get this error message on the next line highlited in bold- application defined or object defined error

[LEFT][COLOR=#333333][FONT=monospace]With Sheets("Data")
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("N:N").AutoFilter 1, "<>" & Sheet5.Range("C2")
      [B].AutoFilter.Range.Offset(1).EntireRow.Delete[/B]
      .AutoFilterMode = False
   End With[/FONT][/COLOR][/LEFT]

 

[Fluff]

You cannot use offset(1) when working on the entire column. try

      Intersect(.UsedRange, .Range("N:N")).AutoFilter 1, "<>" & Sheet2.Range("C2")

 

[VbaHell]

Hi Fluff

I am still battling with this and I have searched the net trying to find a solution

I have columns "A:AC" and want to filter on column "N" and then delete any rows NOT equal to Sheet5.Range("C2")

With Sheets("Data")
      If .AutoFilterMode Then .AutoFilterMode = False
         .[COLOR=#333333][FONT=monospace]Intersect(.UsedRange, .Range("N:N")).AutoFilter 1, "<>" & Sheet2.Range("C2")[/FONT][/COLOR]      
         .AutoFilter.Range.Offset(1).EntireRow.Delete
         .AutoFilterMode = False
   End With

 

[Fluff]

You need to change the sheet reference in the autofilter

 

[VbaHell]

Do you mean like this

With Sheets("Data")
      If .AutoFilterMode Then .AutoFilterMode = False
         Intersect(.UsedRange, .Range("N:N")).AutoFilter 1, "<>" & Sheet2.Range("C2")
         .AutoFilter.Range("N:N").EntireRow.Delete
         .AutoFilterMode = False
   End With

 

[Fluff]

The criteria is currently looking at Sheet2, not sheet5

 

[VbaHell]

I have changed the criteria from Sheet2 to Sheet5

With Sheets("Data")
      If .AutoFilterMode Then .AutoFilterMode = False
         Intersect(.UsedRange, .Range("N:N")).AutoFilter 1, "<>" & Sheet5.Range("C2")
         .AutoFilter.Range("N:N").EntireRow.Delete
         .AutoFilterMode = False
     End With

 

[Fluff]

Why have you changed this line?

.AutoFilter.Range("N:N").EntireRow.Delete