VBA button to filter out transparent cells

[seraphinaex]

Hi everyone,

I've spent two days using my Google-fu trying to find an answer, but I'm stumped.

I have a spreadsheet that I'm using to monitor the progress of a number of projects, sort of Gantt chart-style. The projects are listed down the rows and the months of the year are the columns. Each month, the projects are given a rating (red, green, amber) based on their progress or they're left blank (transparent) if they haven't started/have finished.

I'm trying to create a button for each month of the year that will filter out the projects that haven't started or have finished, to make it easier to focus on the active projects. I thought the easiest way to achieve this was to create an autofilter that would hide any cells that are transparent. However, I've tried lots of variations of things and haven't been able to make it work.

My current version, which just hides everything in the spreadsheet, is:

Private Sub CommandButton14_Click()    Range("AprilStart:AprilEnd").AutoFilter Field:=1, Criteria1:="Interior.ColorIndex <> xlNone", VisibleDropDown:=False
End Sub

Can anyone suggest another way to filter out the transparent cells? Thank you in advance, from a VBA newbie.

 

[FormR]

Hi,

Depends on your version of Excel - but you could try recording a macro whilst you add an auto filter, select "Filter by color" in the filter dropdown and choose "No fill".

 

[seraphinaex]

Depends on your version of Excel - but you could try recording a macro whilst you add an auto filter, select "Filter by color" in the filter dropdown and choose "No fill".

I'm using Office 2010. I'm trying to filter it so that it doesn't show the transparent cells, so I'm not sure that would work?

 

[FormR]

I'm trying to filter it so that it doesn't show the transparent cells

Ah, I see - I would suggest that you might be better using data to indicate if your projects are active or not - like adding a status column (Open,Started,Finished etc), and then filtering on that. It's often a bad idea to use formatting as data.

You could also apply conditional formatting based on the status if you wanted to keep the visual effect.

 

[seraphinaex]

Thanks for the replies, FormR. I get your point about using the formatting - I'll see if I can find another way to make it work, so that I can filter the active projects by month.