filter based on active cell

[danovkos]

Hi all,
i have those codes, but it doesnt works, as good as i wish.
I tried to fix it, but i am not succesfull.
It have to filtering based on cell, where you are active in actual column. With the first part helped me a few weeks ago some people from this forum and i made a lot of versions of this for me. But with this vs. i am dessperate.

what is wrong in this code? - it have to filtering based content of active cell (the content of active cell will be condition for filter).

Sub filter_AktivBunka()

Dim Col As Long
Dim val As Long

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), Operator:=xlAnd
End Sub

And i tried oposite, but it absolut didnt worked. I mean, filtering active column, but without data which equals to content of active cell.


Sub filter_Nie_aktivBunka()

Dim Col As Long
Dim val As Long

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)", Operator:=xlAnd
End Sub

thx for you help

[MaximS]

try that:


Sub filter_AktivBunka()

Dim Col, val, ctr As Long
Dim FiltRng As Range
Dim Errm As String

Col = ActiveCell.Column
val = ActiveCell.Value

ctr = WorksheetFunction.CountBlank(Columns(Col))

If ctr = Rows.Count Then
Errm = MsgBox("Column with Range you trying to Filter is empty.", _
vbCritical + vbOKOnly, "No data in selected column")
Exit Sub
End If



Set FiltRng = ActiveSheet.Range(Cells(1, Col), _
Cells(Cells(Rows.Count, Col).End(xlUp).Row, Col))

If ActiveSheet.AutoFilterMode = True Then
ActiveSheet.AutoFilterMode = False
End If

FiltRng.AutoFilter Field:=1, Criteria1:=(val), Operator:=xlAnd

End Sub

[danovkos]

this code works, but it turn it off my existing auto filter and turn on autofilter only for one column :(
it was long way to create filtering based active column and body of my original code is result of this long work...and i supposed, that this basic code should be to basic to new code. Else it will be here the problem with filtering (switching off and on autofilter) again.

[MaximS]

in that case change that:


Set FiltRng = ActiveSheet.Range(Cells(1, Col), _
Cells(Cells(Rows.Count, Col).End(xlUp).Row, Col))

If ActiveSheet.AutoFilterMode = True Then
ActiveSheet.AutoFilterMode = False
End If

FiltRng.AutoFilter Field:=1, Criteria1:=(val), Operator:=xlAnd


with:


Set FiltRng = ActiveSheet.UsedRange

If ActiveSheet.AutoFilterMode = True Then
ActiveSheet.AutoFilterMode = False
End If

FiltRng.AutoFilter Field:=Col, Criteria1:=(val), Operator:=xlAnd

[danovkos]

this vs
- shows nothing if i filtering number (only for text works)
- change autofilter from my existing to 1st row :( - byt i dont have this alway in 1st row - mostly in other :( - i search something, what will not change my existing autofilter because i can applied filter in column A (only blanks) and i will run this code in column B on active cell. And i need, it will be applied both (the existing before running) and the new one based on active cell

[MaximS]

unfortunately you cannot have 2 separate autofilters in one worksheet but you can always have one which is applying to multiple columns.

sample workbook will help better understand your requirements.

[danovkos]

yes, i want only one autofilter, and i want to use criteria in more then one columns

here is sample file
- is it huge table:
f.e. try to use filter in green column based green data and then during used green, try to filtering based blue data - with your new code...
- try filtering text or number...when i tried it it doesnt works and it change row, where the filter is applied from my 3th row to new 1st row :(

[MaximS]

danovkos,

after playing a bit with your worksheet i've found possible solution.


Sub filter_AktivBunka()

Dim Col As Long
Dim val As String
Dim FiltRng As Range

val = ActiveCell.Value

Select Case ActiveCell.NumberFormat
Case "#,##0.00"
val = Format(val, "#,##0.00")
Case "0"
val = Round(val, 0)
End Select

Col = ActiveCell.Column - 1
Set FiltRng = ActiveSheet.Range("B3:AX" & Rows.Count)

If ActiveSheet.AutoFilterMode = True Then
FiltRng.AutoFilter Field:=Col, Criteria1:=(val)
Else
FiltRng.AutoFilter
FiltRng.AutoFilter Field:=Col, Criteria1:=(val)
Cells(3, Col).Select
End If

End Sub

[danovkos]

maximS thx for your try, but is still doesnt works :(
if i select green or blue data from my wb, it filtering nothing :(
this your last code works for you in my wb? because for me not

[MaximS]

one thing you can try before running the code:

turn off your current autofilter and then try.

[danovkos]

no, turn off autofilter didnt help :(

it can not be used something like my original code, which works? :doh:

f.e.:
this

Sub filter_hide_NA()

Criteria1:="<>#N/A"

Dim Col As Long
Set FiltRng = ActiveSheet.AutoFilter.Range
Col = Selection.Column - FiltRng(1).Column + 1 '<++++++++++++
If FiltRng.Columns.Count = 1 And Col = 0 Then Col = 1
Rem FiltRng.AutoFilter field:=Col, Criteria1:=Flt
FiltRng.AutoFilter Field:=Col, Criteria1:="<>#N/A"


and only define value as active cell and then put it in criteria ?
because this works perfect..

[MaximS]

i've tested posted code on ur workbook and worked fine but if you explain me what is not working in real life example we will try to find solution.

posibly we need to add some more format types to that section:

Select Case ActiveCell.NumberFormat
Case "#,##0.00"
val = Format(val, "#,##0.00")
Case "0"
val = Round(val, 0)
End Select

[danovkos]

ok, look
i try to use this code in this file (atach) and f.e. on exactly selected cell (when you open a wb - it is cell J16). Then i run macro and it hides all data and i can not see, where the filter is applied :(
code is inserted in ThisWorkbook.

I dont know, what i do wrong :banghead: :dunno

[danovkos]

can anyone fix my code?

[Jan Karel Pieterse]

No need for any VBA code. There is a filter by current cell button!
Rightclick toolbar, select customise. Click Commands tab.
Select Data category, drag the autofilter item to a toolbar.
Done!

[danovkos]

ouau :)
nice...
but i dont know, but this function in my huge table 6000row x 100 column works strange :(
f.e. i click on cell K9 and click on autofilter and it filter other column J:J

do you know why?
i have hidden column there, blank rows and so, but i rebuild my filter. I turned it off, celect whole range of my table and turn on. But still the same strange behavior

[Jan Karel Pieterse]

That is odd indeed. Works fine for me. What if you copy everything to a fresh workbook?

[danovkos]

in new WB it works good
what does it mean? :(
where can be problem?

[danovkos]

does it works for you if you turn on filter for f.e. 5 columns where data are only in column 1 to 3 and 5? The 4th col. is blank?
this doesnt works in new WB.

[Jan Karel Pieterse]

In Excel 2003 this works fine for me (I filled cols a,b,c emptied col D, filtered on cell A2 and on cell C4 and on cell E5)

[danovkos]

i thought, that can be because my personal.xls is modified with my codes and macros and...
but i tried it by my coleges and she has the same problem (we have office 2003)
if i have table and one of column in middle table is empty, then this filter applied in other column as i want. I am in column D and after run filter it filtering in col A :(
i dont understund :(

[Jan Karel Pieterse]

I guess Excel doesn't like empty columns in an autofilter area. I don't either <smile>.

[MaximS]

I have tried my code on 2 different operating systems and works fine on English XP but not on Polish Vista.

I guess your OS is Czech.

The problem lies in local value formatting (i.e separators), but so far I couldn't fix it. I need to dig a little bit more.

[danovkos]

it works, but only for my simply tables. If i try it in my huge table with, blank rows, hidden columns ....it doesnt works :(. Then is filter applied in other column not in active.