filter - hide active cell content

[danovkos]

hi all,
pls. how can i change this code, that it filtering the same value as i see (format) not, what is in cell?

now its Works this way:
if i have in column date „21.2.09“. I want hide this date in column.
I use autofilter but i use value 21.2.2009 not 21.02.09 and this doesnt filter nothing.
I need it change this way, that if i use my code it will short year 09 insted 2009 because with 09 it works. If i manualy use custome filter and write 21.2.09 it works.

How can i fix it?

Sub filter_NEOBSAHUJE_AktivBunka()
 
    Dim col, val, ctr As Long
    col = ActiveCell.Column
    val = ActiveCell.Value
    ctr = WorksheetFunction.CountBlank(Columns(col))
        val = ActiveCell.Value
    Set FiltRng = ActiveSheet.AutoFilter.Range
    col = Selection.Column - FiltRng(1).Column + 1 '<++++++++++++
    If FiltRng.Columns.Count = 1 And col = 0 Then col = 1
    FiltRng.AutoFilter Field:=col, Criteria1:=("<>" & val)
End Sub

[MaximS]

try that:

[VBA]
Dim Temp As String, val As String

If Len(val) > 8 Then
Temp = Left(val, Len(val) - 4) & Right(val, 2)
val = Temp
End If
[/VBA]

[danovkos]

No it doesnt works?
It does, what i wanted(change year 2009 to 09) but the results doesnt changed.

But interesting is, that when i open filter, part Custome filter and send enter it works and it filtering the date. Why?

[danovkos]

and now i figured out, that this i can not use because i use this code for filtering all my data. e.g. filtering names and so...
and it will not works for everything

[MaximS]

There was always some problems with passing variable with date to the filter.

[danovkos]

is there any way to fix it?

[mdmackillop]

Can you post a copy of the actual data to be filtered.

[danovkos]

here is my fake table (red column)

[mdmackillop]

How about getting rid of the dates!
[VBA]
Sub Macro2()
col = ActiveCell.Column
Set filtrng = Range("A2:J1622")
ActiveCell.EntireColumn.Cells.NumberFormat = "General"
filtrng.AutoFilter Field:=6, Criteria1:="<>" & CLng(ActiveCell)
ActiveCell.EntireColumn.Cells.NumberFormat = "mmmm yyyy"
End Sub
[/VBA]

[MaximS]

I don't think there is a real way to fix it as the problem is related with how the Excel is localizing the date format.

[danovkos]

How about getting rid of the dates!
[vba]
Sub Macro2()
col = ActiveCell.Column
Set filtrng = Range("A2:J1622")
ActiveCell.EntireColumn.Cells.NumberFormat = "General"
filtrng.AutoFilter Field:=6, Criteria1:="<>" & CLng(ActiveCell)
ActiveCell.EntireColumn.Cells.NumberFormat = "mmmm yyyy"
End Sub
[/vba]

no it doesnt work
here in atach are my regional settings...if it helps

[mdmackillop]

No attachment