Solved: filter criteria in active column

[danovkos]

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]

Worked for me in both/either.

[danovkos]

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]

Using Simon's code. Works with 120 cols x 6500 rows.
[vba]Private Sub CommandButton1_Click()
If ActiveSheet.AutoFilterMode Then Cells.AutoFilter
Columns(ActiveCell.Column).AutoFilter Field:=1, Criteria1:=""
End Sub
[/vba]

[Bob Phillips]

Are you saying that you have two tables and you have selected bith before filtering?

[danovkos]

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]

Can't you post that workbook?

[danovkos]

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]

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

[/VBA]

[danovkos]

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]

Does it produce the result you would be looking for in my attached example?

[danovkos]

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]

-for table in unit
What does this mean?

[danovkos]

sorry for my terrible english

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

[mdmackillop]

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

[/vba]

[Simon Lloyd]

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]

uff, no it doesnt works

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]

If you want to run successive filters then remove the ShowAllData line.

[danovkos]

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]

For successive filters, try the attached