PDA

View Full Version : Solved: filter criteria in active column



danovkos
03-19-2009, 04:00 AM
hi, all
can you help me?
i have macro which filtered data based my condition in active column.
but it sometimes doesnt works.

pls. why ?

f.e. if i have autofilter only in selected range of cell not in whole table it gives me an error -
e:1004
autofilter method of range class failed
or
sometimes it filter not in active column but in previous or next column
Sub filter_show_BLANK()
'
' Macro recorded 14.1.2009 by Vráblik
'
Columns(ActiveCell.Column).AutoFilter Field:=ActiveCell.Column, Criteria1:=""

my code:

Bob Phillips
03-19-2009, 04:37 AM
Presumably, because you are using the column number as the filter column offset. So, if you select column 5 only, you will be trying to filter on the 5th column of a one column selection - boom!

Simon Lloyd
03-19-2009, 04:39 AM
You are only selecting i column to filter on yet in the field you are targeting a second column!, use this:Columns(ActiveCell.Column).AutoFilter Field:=1, Criteria1:=""

Bob Phillips
03-19-2009, 04:40 AM
I am not sure why you don't just use 1, but you could try



Sub filter_show_BLANK()
'
' Macro recorded 14.1.2009 by Vráblik
'
Columns(ActiveCell.Column).AutoFilter Field:=Selection.Column - ActiveCell.Column + 1, Criteria1:=""
End Sub

Simon Lloyd
03-19-2009, 04:40 AM
Presumably, because you are using the column number as the filter column offset. So, if you select column 5 only, you will be trying to filter on the 5th column of a one column selection - boom!Lol!, glad i didn't bother to run that line of code then, i'm using a laptop and that would have been a nasty accident!!!:rotlaugh:

Bob Phillips
03-19-2009, 04:43 AM
Lol!, glad i didn't bother to run that line of code then, i'm using a laptop and that would have been a nasty accident!!!:rotlaugh:

Yeah, he owes me a new, top of the range, quad processor laptop now!

Simon Lloyd
03-19-2009, 04:46 AM
Yeah, he owes me a new, top of the range, quad processor laptop now!Funny, i forgot about the loss in translation, i was sure i said NASTY not EXPENSIVE! :p

Bob Phillips
03-19-2009, 04:51 AM
Well, it was nasty when it happened, but these things have repurcussions.

danovkos
03-19-2009, 05:09 AM
thx for all your advises but i dont have my solution, becaouse any of your code doesnt works as i want.
Maybe i bad express my self.
I want macro, which will always show only blanks cell, but only in column where i am - which is active, where is cursor.
Doesnt matter if is freezet panes, if is autofilter only on part of table or anythning...
can you help me with this?
maybe som define range before it try filtering?

Simon Lloyd
03-19-2009, 05:13 AM
You are only selecting i column to filter on yet in the field you are targeting a second column!, use this:Columns(ActiveCell.Column).AutoFilter Field:=1, Criteria1:=""Whats wrong with this line?

danovkos
03-19-2009, 05:15 AM
Whats wrong with this line?
this code filtered blanks but always in 1 column, but i need filtered column where i click, where is my cursor
do you know what i mean?

Bob Phillips
03-19-2009, 05:22 AM
Sub filter_show_BLANK()
'
' Macro recorded 14.1.2009 by Vráblik
'
Activesheet.UsedRange.AutoFilter Field:=ActiveCell.Column, Criteria1:=""
End Sub

danovkos
03-19-2009, 05:25 AM
it gives me the same error
:(

Bob Phillips
03-19-2009, 05:33 AM
Work for me.

Do you have data in columns A forward?

danovkos
03-19-2009, 05:43 AM
I dont want to have always data in column A
look in atach.
first - i click on cell I3
second - run the macro which have to filtering column I and show me the blanks
but maybe on next time i click in column G for example on cell G10 and i want to filtering column G

Simon Lloyd
03-19-2009, 05:44 AM
I suspect the fault is that filters are not being turned off before you click elsewhere.

Simon Lloyd
03-19-2009, 05:45 AM
No attachment!

danovkos
03-19-2009, 05:53 AM
one more time, atach
i can not upload it :(

Bob Phillips
03-19-2009, 06:02 AM
Sub filter_show_BLANK()
'
' Macro recorded 14.1.2009 by Vráblik
'
With ActiveCell

.CurrentRegion.Select
.CurrentRegion.AutoFilter Field:=.Column - .CurrentRegion.Cells(1, 1).Column + 1, Criteria1:=""
End With
End Sub

danovkos
03-19-2009, 06:06 AM
ok
how define in code, what i want
so if i have more as one table but autofilter is turned on only for one table, maybe from column E to H and i click in H it will show me the blank cell in this column. Of course it will works only if is the autofilter turned on for this column

danovkos
03-19-2009, 06:12 AM
yes this last code works for more as one table (but only in one is active autofilter), but if i have only one table - autofilter for whole table, i want to use the same macro...but here it filtering other column as i want. Not this, which is active.
And little detail, it select range of table and after finishing macro it is still whole selected..
but i think, that you are close to solution

Bob Phillips
03-19-2009, 06:25 AM
Worked for me in both/either.

danovkos
03-19-2009, 07:43 AM
yes, it will works for me too, if i create new fiel with 2 tables and i try it there. If i give filter on part of data, it works, if i will only one filter it works too.
BUT, if i try it on my huge data with 6000 rows and 80 columns - there i have only one filter and there it filtering other column :(
i don know why if it is working for new createt small file

mdmackillop
03-19-2009, 01:45 PM
Using Simon's code. Works with 120 cols x 6500 rows.
Private Sub CommandButton1_Click()
If ActiveSheet.AutoFilterMode Then Cells.AutoFilter
Columns(ActiveCell.Column).AutoFilter Field:=1, Criteria1:=""
End Sub

Bob Phillips
03-19-2009, 01:50 PM
Are you saying that you have two tables and you have selected bith before filtering?

danovkos
03-20-2009, 03:36 AM
Are you saying that you have two tables and you have selected bith before filtering?

sorry, what do you mean with "bith"?

in my huge DB i have data from column A to column BW
from row 1 to row 4400.
there are blank rows between data too
i have freezen pan
i have autofilter always turned on in line 3 from column A to BW

i click in cell J8 and run macro
result: select part of table (J4:O3709) - 3710 is blank row
- filter applied in column A - criterium ""

Bob Phillips
03-20-2009, 04:47 AM
Can't you post that workbook?

danovkos
03-20-2009, 05:06 AM
i tried it but i can not change it to fake data :(
it is huge
and if i deleted all and give there only a few of date, it works good
so i am cluelles

mdmackillop
03-20-2009, 05:26 AM
Try this

Private Sub CommandButton1_Click()
On Error Resume Next
ActiveSheet.ShowAllData
Range("A3:BW5000").AutoFilter Field:=Selection.Column, Criteria1:="="
End Sub

danovkos
03-20-2009, 05:58 AM
no this doesnt works
but in this my huge table works my old code:

i will now call it "code 1"

Columns(ActiveCell.Column).AutoFilter Field:=ActiveCell.Column, Criteria1:=""

maybe is solution something like

do code1
and if it is error
do code 2

code 2
will be

With ActiveCell
.CurrentRegion.Select
.CurrentRegion.AutoFilter Field:=.Column - .CurrentRegion.Cells(1, 1).Column + 1, Criteria1:=""
Criteria1:=""
End With

i tried it like this, but its no good because it does both codes :(



Sub filter_show_BLANK()
Columns(ActiveCell.Column).AutoFilter Field:=ActiveCell.Column, Criteria1:=""

On Error Resume Next

With ActiveCell
.CurrentRegion.Select
.CurrentRegion.AutoFilter Field:=.Column - .CurrentRegion.Cells(1, 1).Column + 1, Criteria1:=""
Rem If ActiveSheet.AutoFilterMode Then Cells.AutoFilter
Rem Columns(ActiveCell.Column).AutoFilter Field:=ActiveCell.Column, Criteria1:=""
End With

mdmackillop
03-20-2009, 06:00 AM
Does it produce the result you would be looking for in my attached example?

danovkos
03-20-2009, 06:07 AM
yes i tried your atach. and it works, BUT,
i need something, what will be universal for any possibility
- for small table
- for huge table
- for devided table - where i have filter only on a part of data
-for table in unit
...
and i run the macro from toolbar..so it is not necessary to insert button

and this your code doesnt works if you cancel the autofilter for whole table and aplly autofilter o only for few columns

mdmackillop
03-20-2009, 06:11 AM
-for table in unit
What does this mean?

danovkos
03-20-2009, 06:15 AM
sorry for my terrible english

table, which is not devided to parts, where is autofilter for whole first line of one table

mdmackillop
03-20-2009, 07:21 AM
Sub Filtering()
Dim FiltRng As Range
Dim Col As Long
On Error Resume Next
ActiveSheet.ShowAllData
Set FiltRng = ActiveSheet.AutoFilter.Range
Col = Selection.Column - FiltRng(0).Column
If FiltRng.Columns.Count = 1 And Col = 0 Then Col = 1
FiltRng.AutoFilter field:=Col, Criteria1:=""
End Sub

Simon Lloyd
03-20-2009, 07:30 AM
Testing workbook upload used range A1:BZ4400

My workbook below just has data in every cell in the above range, which is more than yours and is 919kb, therefore my suggestion is to zip yours and upload it (shouldn't be a problem) or export your code from the workbook and upload that and your workbook without the VBA code, then we can help you fully!

danovkos
03-20-2009, 07:34 AM
uff, no it doesnt works :banghead:

and when i look at the code... i think if it will works it will cancel all criteria in filter...with command „show all“

why i want this:
i use this for my daily work. I work with many tables and i have macros (toolbar) to filtering data as i want. I want to quck filtering data. Sometimes i want to filtering based on more as one criterium in filter. So i click in column based on i want to filtering, run first macro, then i click in other column an run other macro. I adding many criteria to filtering(f.e. i have macros for: blanks, nonblaks, =NA, <>NA ....)

i dont know what i have to do :(

mdmackillop
03-20-2009, 07:43 AM
If you want to run successive filters then remove the ShowAllData line.

danovkos
03-20-2009, 07:57 AM
yes, but it still doesnt works :(
but i have other question to this code



Columns(ActiveCell.Column).AutoFilter field:=ActiveCell.Column, Criteria1:=""


why this code doesnt works for autofilter, which is doesnt use for all table but only for part of table. If i use this code for autofilter where is autofilter for whole table it works great, but if is table from A to W, but i have filter in first line only for B to D, so it doesnt works- It give criterium to the next column :(. WHY?
In code is written "ActiveCell.Column" and this is always the cell where i am or? :(
i dont understund it

mdmackillop
03-20-2009, 07:58 AM
For successive filters, try the attached

mdmackillop
03-20-2009, 08:01 AM
yes, but it still doesnt works :(
but i have other question to this code



Columns(ActiveCell.Column).AutoFilter field:=ActiveCell.Column, Criteria1:=""


why this code doesnt works for autofilter, which is doesnt use for all table but only for part of table. If i use this code for autofilter where is autofilter for whole table it works great, but if is table from A to W, but i have filter in first line only for B to D, so it doesnt works- It give criterium to the next column :(. WHY?
In code is written "ActiveCell.Column" and this is always the cell where i am or? :(
i dont understund it


The activecell.column is the column nummber, as you know. The Field is the count from the left of the Filter Range, so Filtering E-H, Column F is Field 2, ActiveCell.Column is 6. This is catered for in my last posts.

danovkos
03-20-2009, 08:01 AM
no, doesnt works
if i turned off autofilter and turned on it only for a f.e. column B:D
it does what i wrote before. It filtering next column not active column
??? why

mdmackillop
03-20-2009, 08:04 AM
no, doesnt works
if i turned off autofilter and turned on it only for a f.e. column B:D
it does what i wrote before. It filtering next column not active column
??? why

For clarity, to which post are you responding?

Simon Lloyd
03-20-2009, 08:06 AM
Danovkos, please make some efforts as i explained in a previous post to post your workbook!

mdmackillop
03-20-2009, 08:09 AM
Have you tried stepping through the code to identify the error?

danovkos
03-20-2009, 08:11 AM
Danovkos, please make some efforts as i explained in a previous post to post your workbook!
i tried it but it doesnt effect, because of the code for me doesnt works for atached filters.xls too.
As i wrote.
It works when i open it, but if i change the autofilter only for a part of table, it stops works and filtering column next active, not active.
i am really sorry for lost your time :( i only try to fix my codes

danovkos
03-20-2009, 08:13 AM
Have you tried stepping through the code to identify the error?
yes, but it doesnt show any error. It only after chagne of filter filtering other column as i need.

mdmackillop
03-20-2009, 08:17 AM
Which version of Excel are you using? Not that I think it should make a difference.

danovkos
03-20-2009, 08:21 AM
Which version of Excel are you using? Not that I think it should make a difference.
Office 2003 with SP3

mdmackillop
03-20-2009, 08:28 AM
I can test in that later.

One last attempt for now. Try this version in the Userform example

Sub Filtering2(Flt As String)
Dim FiltRng As Range
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
FiltRng.AutoFilter field:=Col, Criteria1:=Flt
End Sub



If that fails, try changing the indicated 1 value to 0 to see if it helps

mdmackillop
03-20-2009, 09:43 AM
I've tried this in 2003 and 2007 and it works in both. Some error handling has been added. If this isn't it, I don't know what else to suggest.

danovkos
03-22-2009, 11:48 PM
yes, yes, yes :)
it works perfec...and the second code with useform is great too. I give there my most used criteria and will always use it :))
thanke you very very much to all. :rotlaugh: