VBA filtering?

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,031
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,031
Office Version
  1. 365
Platform
  1. Windows
Unfortunately I cant add that addin due to our IT setup here :(

Does this screenshot help?

1600856634307.png
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,369
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Oct 18, 2005
Messages
1,031
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 3, 2012
Messages
6,369
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Oct 18, 2005
Messages
1,031
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

:giggle:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

Well-known Member
Joined
Oct 18, 2005
Messages
1,031
Office Version
  1. 365
Platform
  1. Windows
Should "Apples" be entered in B2 as the criteria?

Seems to fail at;

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

1600865565430.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,214
Messages
5,594,882
Members
413,947
Latest member
gizmolucy

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
Top