filtering collums as well as rows? possible?

[dusty]

I have a table set up which lists project names their relevant buisness stream and then personal working on which projects this could be multiple.

each person is a collum and can be working on multiple projects across multiple buisness streams.

I have a filter set up on the buisness stream column so that it filters all the projects so that only buiness stream A is listed or B ect

However this means that the columns for person c and e are shown even though they are not working on projects in that stream. Is there a way to use th eintial filter to filter the collumns too.

It is marked in a cell beneath their name which streams they work for. Seperate cell for each stream and name merged accross all cells.

So to sum up I want to be able to select stream A from the filter and it to filter the rows beneath it (already achieved this) and hide the columns of people that do not work in this stream.

Any help would be great or point me in the right direction to some relevant code techniques (im happy to trawl through information just would like a kick in the right direction)

cheers Dusty

 

[Atomic Shrimp]

each person is a collum

This is the root cause of your difficulties - each person should not be a column, in a normalized database.

The chief reason for that is: when people leave or join, you don't want to have to be reacting to this by creating new columns in your tables, and rewriting your entire application to talk to those new columns.

I would recommend reading up (here and elsewhere on the web) about normalization before you proceed any further...

 

[dusty]

Hi Atomic Shrimp

Thanks for you reply I understand where you are coming from.

Basically just moved into a new job the current document was not designed by me and is temporary before I get the go ahead to port it all to a central data base need to wait for access rights ect

So i was more interested if there was a work around maybe an inefficient way of getting this to work just to keep myself occupied and maybe learn a few codeing skills.

cheers dusty

 

[Atomic Shrimp]

Probably the simplest way to do it, if you are forced to work with the data in this way, is to display the table in a datasheet subform and set the columns to visible or invisible as appropriate for the person selected in the main form.

 

[chergh]

This is the root cause of your difficulties - each person should not be a column, in a normalized database.

Well as this is excel thats not really very relevant .

With a few quick thoughts you could use the intersect method for each row returned by your filter and loop through your columns and if the value of the range returned by the intersect method is empty then hide that column.

 

[Atomic Shrimp]

Bah! Really sorry. - I completely missed that. Not sure how I got here without reading the forum name, I thought we were talking Access.

If the number of 'people columns' is fairly small, isn't it just as easy to shrink the width of the irrelevant ones to zero?

 

[chergh]

I think they are wanting it dynamic based on whatever filter criteria is chosen which will require vba in which case it's as easy to hide as it is to change width to 0.

 

[dusty]

No problem atomic Shrimp

and yes to both you and chergh I understand that the simplest way to achieve it would be to set column width to equal zero.

But how to link this oporation to the existing filter??

and perhaps the bigger problem getting this to reset once the filter is removed or changed.

I could easily find the code to set a specific column or a range of coullumns to width of zero.

But to get this oporation to be dynamic and be controlled by the existing filter is where I will have troubles.

Thanks Dusty

 

[dusty]

OK scrap my last post the soulution I require does not actualy require to be that dynamic.

As this is just a temporary medium for the data to be in the table will not get any bigger.

So I know exactly what collumns need to be hidden for each filter option.

Is there a way that I can use each filter option kinda like a command button to hide specific collumns in each case.

And then I guess on change of the filter uhide every column and hide sepcific cells.

Hope this makes the situation a little clearer.

 

[dusty]

Ok so I scraped the idea of having it linked to the original filter.

Instead I thought about having two command buttons in conjunction with the filter one to shrink and one to reset.

The idea being the shrink button would hide any column that does not have a value in it ie hide anything that =0

this is the code I have so far and it almost works.


Private Sub CommandButton1_Click()
Call Hide_EmptyColumns
End Sub

Sub Hide_EmptyColumns()
'To hide columns with no data in rows 11:139
Dim col As Range
Application.ScreenUpdating = False
With ThisWorkbook.Sheets("Engineering")
For Each col In .Range("E11:CW139").Columns
col.EntireColumn.Hidden = _
Application.Sum(col) = 0
Next
End With
Application.ScreenUpdating = True
End Sub

However the problem comes when I apply the filter because of course then I don't want it to scan from rows 11-139 I want it to miss out the filtered results.

Could someone help me modify the code so that it only looks at the filtered rows.



Cheers Dusty