<sausages>
I couldn't believe it. But it does actually work.
Any MSExcel gurus?
I'm trying to write a formula that a filter will use, but in order for the filter to work properly I need the cell to have data or be null: not contain an empty string (ie "")
if(Filter=True,"Use This Text",<null>)
Anyone know what I put into the <null> to actually get a null entry?
<sausages>
I couldn't believe it. But it does actually work.
I'll try english, but don't hold out for any better explanation.
...and it works if the data in the named range "FilterListBy" is typed in. But I want to have it entered via a picklist/formula in another cell somewhere, so I have the formula:Code:Private Sub SubFilterList() If FilterMode = True Then ShowAllData Range("FullSheet").AdvancedFilter _ Action:=xlFilterInPlace, _ CriteriaRange:=Range("FilterListBy") Range("B11").Select End Sub
If(UseFilter=True,"Filter Text","")
Which inserts a null string if I don't want to filter it... of course none of the data in the list is a null string, so the filter does what it's told and filters out all the records.
(oh and I tried "*" for a wild card, but that dosn't seem to work either)
Try using two double quotes:
=IF(filter,"stuff","")
just saw your reply... let me figure this out then...
OK - if you're doing it in VB, why not use a conditional to set the criteria range to null. I have a funny feeling that if you enter a formula in a cell, the cell cannot be null. Well, it's more than a feeling - microsoft actually say it themselves (and it sort of make sense - null means nothing in the cell, a formula is not nothing).
So you have to validate the filter list to make sure you don't have any blank cells before you use it as a filter.
Solved: it was to do with boolean values rather than null values as i first thought...well, that and it didn't scroll up to the top of the list once it had filtered it, so it looked like there was nothing there
Thanks anyway Geoff.
~G~
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks