Solved: Autofilter problem

**ukdane** · 12-17-2008, 04:08 AM

I've created a button that the end user can press, in order to run a Macro, that will filter out a series of criteria, using the autofilter.
The code is below:
[VBA]Sub fjernsk?n()
Dim thissheet As String
Dim myrange As String
On Error GoTo Cancel
thissheet = ThisWorkbook.ActiveSheet.Name
myrange = ActiveCell.Address
Selection.AutoFilter Field:=4, Criteria1:="<>HUSK SK?N", Operator:=xlAnd _
, Criteria2:="<>HUSK LUK", Operator:=xlAnd _
, Criteria3:="<>KLAR TIL ARKIVERING"
Range("E3").Select
ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
ActiveSheet.Range(myrange).Select
Cancel:
End Sub[/VBA]

The problem is that if one (or more) of the Criteria is missing (which could be possible) the code stops working.

How can I change this code so that it will filter out any/all of the occurances of the three listed criteria regardless of whether the criteria appears in the column or not?

Cheers

**ukdane** · 12-17-2008, 04:43 AM

Secondly, Why won't the following code sort the autofilter in column L(Field 12)?

[VBA]Sub pudatosort()
Dim dagsdato As Date
Dim pudato As String
Dim mydato As String
dagsdato = Date
pudato = dagsdato + 1
mydato = ActiveSheet.Range("L3").Value
Selection.AutoFilter Field:=12, Criteria1:= mydato
End Sub[/VBA]

it wont sort it if I chnage the criteria to pudato, either.

**Bob Phillips** · 12-17-2008, 05:10 AM

What do you mean by stops working? The filter shows nothing, the code errors?

Got an example workbook?

**ukdane** · 12-17-2008, 07:56 AM

Hi xld,
I get an error code, and the reason I get it is because criteria 3 doesn't exist as an option in the fields.
So what I need is some way for the code to check if any of the criteria are available, and include them in the filter if they are visible.
If I remove the "On Error GoTo Cancel" line, then I get:
"Run-time error 1004:
Application-defined or object-defined error"

Obviously, with the On Error line included, the marco just skips, and nothing happens.

**Bob Phillips** · 12-17-2008, 08:38 AM

The problem is that you have three conditions, not that they are missing. Autofilter only supports 2.

**Benzadeus** · 12-17-2008, 08:43 AM

Aswering your second question, about this part of the code
[vba]
Selection.AutoFilter Field:=12
[/vba]

refers to filter at column L ONLY if your autofilter begins at column A.

Ex: if you have set an autofilter from columns D:G and writes
[vba]
Selection.AutoFilter Field:=2
[/vba]
this will refer to the filter at column E.

Edit: If you are getting the error and the autofilter begins at column A, it may be because of xld's explanation.

**ukdane** · 12-17-2008, 09:15 AM

XLD: Ah! Is there anyway around this; Is there anyway to filter 3 (or more) criteria? (Check what is in field D on each row, and if it is the same as the criteria text, then hide the row?)

Benzadeus: The autofilter does start at column A, and in this instance, there is only one criteria, it just doesn't show any results.

**Benzadeus** · 12-17-2008, 09:21 AM

Benzadeus: The autofilter does start at column A, and in this instance, there is only one criteria, it just doesn't show any results.

Well, you got me.

**Bob Phillips** · 12-17-2008, 09:41 AM

Add a formula to test the conditions and filter that

[vba]

Sub fjernsk?n()
Dim thissheet As String
Dim myrange As String
Dim LastRow As Long

With ActiveSheet

On Error Resume Next
.AutoFilter
On Error GoTo 0
thissheet = .Name
myrange = ActiveCell.Address
.Columns(5).Insert
.Range("E1").FormulaR1C1 = "Temp"
.Range("E2").FormulaR1C1 = _
"=AND(RC[-1]<>""KLAR TIL ARKIVERING"",RC[-1]<>""HUSK LOK"",RC[-1]<>""HUSK SK?N"")"
.Columns(5).AutoFilter Field:=1, Criteria1:="=TRUE"

ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
.Range(myrange).Select
End With
End Sub
[/vba]

**ukdane** · 12-17-2008, 09:51 AM

Good idea, I'll try that.

Can I hide the new column, so that the enduser isn't confused by it?

Also, why have you added a Dim LastRow as Long?

**Bob Phillips** · 12-17-2008, 10:13 AM

Yes you could do that.

The Dim LastRow was a mistake, I started thinking of something else.

**Benzadeus** · 12-17-2008, 10:17 AM

[VBA]
.Columns(5).Hide
[/VBA]

Wait xld... in your example, didn't you copy the formula only to [E2]? Shouldn't it be copied [E2:En]?

**Bob Phillips** · 12-17-2008, 12:26 PM

You are right, that was what LastRow was for

[vba]

Sub fjernsk?n()
Dim thissheet As String
Dim myrange As String
Dim LastRow As Long

With ActiveSheet

On Error Resume Next
.AutoFilter
On Error GoTo 0
thissheet = .Name
myrange = ActiveCell.Address
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
.Columns(5).Insert
.Range("E1").FormulaR1C1 = "Temp"
.Range("E2").Resize(LastRow - 1).FormulaR1C1 = _
"=AND(RC[-1]<>""KLAR TIL ARKIVERING"",RC[-1]<>""HUSK LOK"",RC[-1]<>""HUSK SK?N"")"
.Columns(5).AutoFilter Field:=1, Criteria1:="=TRUE"

ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
.Range(myrange).Select
End With
End Sub
[/vba]

**ukdane** · 12-18-2008, 12:57 AM

xld: Thanks.

Any idea as to why the second problem (with the date) doesn't work.
It's as if the criteria choice has reformatted the date, and as a result, can't match the criteria with the actual data.

The two results I get are as follows.
If I use <> then it shows the entire list,
If I use anything else =, <, >, or combination of <= or => then it doesn't show ANY results.

**ukdane** · 12-18-2008, 01:07 AM

xld: I've just applied your code, and I'm getting a runtime error
1004: Application-defined or Object-defined error on the following line:
[VBA].Range("E2").Resize(LastRow - 1).FormulaR1C1 = _
"=AND(RC[-1]<>""KLAR TIL ARKIVERING"",RC[-1]<>""HUSK LUK"",RC[-1]<>""HUSK SK?N"")"
[/VBA]

I'm using Excel 2000 and Excel 2003.

**Bob Phillips** · 12-18-2008, 02:14 AM

I have just tried it again, and we do need one change at least

[vba]

Sub fjernsk?n()
Dim thissheet As String
Dim myrange As String
Dim LastRow As Long

With ActiveSheet

On Error Resume Next
.AutoFilter
On Error GoTo 0
thissheet = .Name
myrange = ActiveCell.Address
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
.Columns(5).Insert
.Range("E1").FormulaR1C1 = "Temp"
.Range("E2").Resize(LastRow - 1).FormulaR1C1 = _
"=AND(RC[-1]<>""KLAR TIL ARKIVERING"",RC[-1]<>""HUSK LOK"",RC[-1]<>""HUSK SK?N"")"
.Columns(5).AutoFilter Field:=5, Criteria1:="=TRUE"

ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
.Range(myrange).Select
End With
End Sub
[/vba]

Do you have a Danish version of Excel or English? If Danish, try

[vba]

.Range("E2").Resize(LastRow - 1).FormulaR1C1 = _
"=AND(RC[-1]<>""KLAR TIL ARKIVERING"";RC[-1]<>""HUSK LOK"";RC[-1]<>""HUSK SK?N"")"
[/vba]

instead.

**ukdane** · 12-18-2008, 02:23 AM

My version is in English, but other users use a Danish verion. I'll try changing the comma to a semi-colon and see if it helps.

(It's a pain that some users use a DK and others a GB version!)

**Bob Phillips** · 12-18-2008, 02:32 AM

If yours is English, then it should work for you.

**ukdane** · 12-18-2008, 03:19 AM

Found the error
[VBA] .Range("E2").Resize(LastRow - 1).FormulaR1C1 = _
"=AND(RC[-1]<>""KLAR TIL ARKIVERING"",RC[-1]<>""HUSK LOK"",RC[-1]<>""HUSK SK?N"")" [/VBA]

The 2 was missing, so it read:
[VBA] .Range("E").Resize(LastRow - 1).FormulaR1C1 = _
"=AND(RC[-1]<>""KLAR TIL ARKIVERING"",RC[-1]<>""HUSK LOK"",RC[-1]<>""HUSK SK?N"")" [/VBA]

Any clues about the date not working in the second macro I posted?

Thread: Solved: Autofilter problem

Thread Tools

Display

Solved: Autofilter problem

Posting Permissions