VBA button to filter out transparent cells

seraphinaex

New Member
Joined
Jan 23, 2018
Messages
3
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:

Code:
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,219
Office Version
365
Platform
Windows
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

New Member
Joined
Jan 23, 2018
Messages
3
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,219
Office Version
365
Platform
Windows
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

New Member
Joined
Jan 23, 2018
Messages
3
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.
 

Forum statistics

Threads
1,082,335
Messages
5,364,696
Members
400,811
Latest member
MSBINinja

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top