MS Excel: I need section heading rows to remain when autofiltering?

[EoinWALSH]

I have a problem in relation to filters in excel. I have a list of about 2000 rows, with the rows being divided into four sections. I want to filter all columns. When i use 'Autofilter' in excel it removes the rows which give the section name once a value has been selected.


So in other words, when the data is filtered i want the 4 section headings to remain at all times. Is there any VB code that will achieve this. Thanks in advance.


Regard,

Eoin Walsh


e.g.
section 1 (row 1)
row 2
row 3
row 4
section 2 (row 5)
row 6
row 7
row 8
section 3 (row 9)
row 10
row 11
row 12
section 4 (row 13)
row 14
row 15
row 16


Example is attached

 

[GlennUK]

I recommend that you re-organise your data so that your list is one single table, with one of the fields ( columns ) showing what the section is.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[EoinWALSH]

thanks Glen, Yes i thought of that but i just have some space requirements...Would there be any way i could have a hidden column and have a macro(VB) that runs when a filter arrow is pressed, so it inserts the line??thanks eoin

 

[Gavona]

You could use an advanced filter instead. Criteria cells above the data. With this approach you can have OR conditions to achieve conditions such as:
If ColA starts with "Section"
or
(ColC = "Widget Department" AND ColD>55 )
You may want to populate the criteria cells using validation lists - so you have drop down arrows quite similar to Autofilter.
Once you have this working manually (ie after changing criteria to have to tell excel to apply the advanced filter) you could look at using code to automatically detect that a criteria cell has changed and apply the advanced filter.

Or using autofilter you will have to put a value in whatever column you are filtering in the Section heading rows (possibly formatted white text to hide it). Then use a custom criteria (which gives be ability to have multiple conditions in a single column).




Gavin

 

[GlennUK]

You could have the section column very narrow ( not hidden ), and then try adding Subtotals ( menu command Data/Subtotal ) as each change of Section, which would insert a line at the appropriate places ( untick Summary Below Data checkbox to get subtotal rows above each section ).

Have the section column in column A, and choose an item to subtotal on the right of the table, so that long subtotal titles flow into cells to the right of column A.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.