PDA

View Full Version : filter - hide active cell content



danovkos
08-14-2009, 12:55 AM
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
08-14-2009, 04:39 AM
try that:


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

danovkos
08-14-2009, 05:03 AM
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
08-14-2009, 05:09 AM
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
08-14-2009, 05:23 AM
There was always some problems with passing variable with date to the filter.

danovkos
08-14-2009, 05:27 AM
is there any way to fix it?

mdmackillop
08-14-2009, 05:47 AM
Can you post a copy of the actual data to be filtered.

danovkos
08-14-2009, 06:32 AM
here is my fake table (red column)

mdmackillop
08-14-2009, 07:57 AM
How about getting rid of the dates!

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

MaximS
08-14-2009, 08:38 AM
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
08-16-2009, 10:50 PM
How about getting rid of the dates!

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


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

mdmackillop
08-17-2009, 12:11 AM
No attachment