Custom format as ;;; to make text invisible but doesn't work with filter?

[Melimob]

Hi

I thought I had found an amazing solution to an issue whereby a column I wanted to filter had blank rows in between.
I autofilled the rows with the value above but as I didn't want the text to show no matter what the cell colour was changed to, I formatted the cells using custom ;;;

this worked great BUT...

Now that I'm filtering, it doesn't find those formatted cells? Only if I use 'contains' as the filter and then type the text but I don't want the user to have to do this each time?

Any advice gratefully received!

thank you

 

[Melimob]

SOLVED

I played around with it and managed to figure it out so posting the final code here in case it helps anyone else:

Private Sub HideFont()
    Dim cell As Variant
    Dim rng As Range
    Set rng = Range("Table1[Country]")
    For Each cell In rng
       If cell.Font.Color <> vbBlack Then
       cell.Font.Color = cell.Interior.Color
       End If
    Next cell
End Sub


Sub UnhideFont()
    Dim cell As Variant
    Dim rng As Range
    Set rng = Range("Table1[Country]")
    For Each cell In rng
        If cell.Font.Color <> vbBlack Then
        cell.Font.Color = 50
       End If
    Next cell
End Sub

then my toggle is:

Private Sub ToggleButton2_Click()
If ToggleButton2.Value = True Then
 'This area contains the things you want to happen
'when the toggle button is not depressed
UnhideFont
Else
'This area contains the things you want to happen
'when the toggle button is depressed
 HideFont
  End If


End Sub

thank you all!