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
[vba]Sub filter_show_BLANK()
'
' Macro recorded 14.1.2009 by Vráblik
'
Columns(ActiveCell.Column).AutoFilter Field:=ActiveCell.Column, Criteria1:=""
[/vba]
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:[VBA]Columns(ActiveCell.Column).AutoFilter Field:=1, Criteria1:=""[/VBA]

**Bob Phillips** · 03-19-2009, 04:40 AM

I am not sure why you don't just use 1, but you could try

[vba]

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
[/vba]

**Simon Lloyd** · 03-19-2009, 04:40 AM

Originally Posted by xld

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!!!

**Bob Phillips** · 03-19-2009, 04:43 AM

Originally Posted by Simon Lloyd

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!!!

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

**Simon Lloyd** · 03-19-2009, 04:46 AM

Originally Posted by xld

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!

**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

Originally Posted by Simon Lloyd

You are only selecting i column to filter on yet in the field you are targeting a second column!, use this:[vba]Columns(ActiveCell.Column).AutoFilter Field:=1, Criteria1:=""[/vba]

Whats wrong with this line?

**danovkos** · 03-19-2009, 05:15 AM

Originally Posted by Simon Lloyd

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

[vba]

Sub filter_show_BLANK()
'
' Macro recorded 14.1.2009 by Vráblik
'
Activesheet.UsedRange.AutoFilter Field:=ActiveCell.Column, Criteria1:=""
End Sub
[/vba]

**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

[vba]

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
[/vba]

**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

Thread: Solved: filter criteria in active column

Thread Tools

Display

Solved: filter criteria in active column

Posting Permissions