VBA filtering?

[MOB]

I have a spreadsheet that has sections of data which I want to filter certain sections based on a dropdown. I dont think standard filtering will work with this.

The data is structured like this;

Option A

code

Apples

Option B

code

Oranges

Option C

code

Apples

Option D

code

Apples

Option E

code

Bananas

Option F

code

Oranges

And when filtered for "Apples" it should look like this, ie it should display the entire section of the data (I need the row below apples to be included);

Option A

code

Apples

Option C

code

Apples

Option D

code

Apples

 

[dmt32]

Hi,
would it be possible to post copy of your worksheet with dummy data using MrExcel Addin:XL2BB - Excel Range to BBCode

Dave

 

[MOB]

Unfortunately I cant add that addin due to our IT setup here

Does this screenshot help?

 

[dmt32]

That’s a shame, makes problem solving tad easier.

off top of my head, cannot visualise a solution for your requirement but will have a quiet think & mean time, maybe another can offer some suggestion.

Dave

 

[MOB]

Thanks Dave - my basic thought was some code to search for "apples" and show the 4 rows above, and the 1 row below each time it finds it. But my VBA skills are nowhere near that level

 

[dmt32]

Thanks Dave - my basic thought was some code to search for "apples" and show the 4 rows above, and the 1 row below each time it finds it. But my VBA skills are nowhere near that level

VBA not the issue - just figuring out practical approach?

Dave

 

[MOB]

 

[Fluff]

How about

Sub MOB()
   Dim Rng As Range
   Dim Ar As Areas
   
   With Range("A:A")
      .Replace Range("B2"), True, xlWhole, , False, , False, False
      Set Ar = .SpecialCells(xlConstants, xlLogical).Areas
      .Replace True, Range("B2"), xlWhole, , False, , False, False
   End With
   Range("4:" & Range("A" & Rows.Count).End(xlUp).Offset(1).Row).EntireRow.Hidden = True
   For Each Rng In Ar
      Rng.Offset(-4).Resize(6).EntireRow.Hidden = False
   Next Rng
End Sub

 

[MOB]

Should "Apples" be entered in B2 as the criteria?

Seems to fail at;

Rng.Offset(-4).Resize(6).EntireRow.Hidden = False

 

[Fluff]

Should "Apples" be entered in B2 as the criteria?

Yes it should, apologies for not mentioning that.
Did you get that error with a search term in B2?