Solved: Autofilter problem

[ukdane]

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

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]

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

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

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

[Bob Phillips]

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

Got an example workbook?

[ukdane]

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]

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

[Benzadeus]

Aswering your second question, about this part of the code

Selection.AutoFilter Field:=12


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

Selection.AutoFilter Field:=2

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]

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]

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]

Add a formula to test the conditions and filter that



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

[ukdane]

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]

Yes you could do that.

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

[Benzadeus]

.Columns(5).Hide


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

[Bob Phillips]

You are right, that was what LastRow was for



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

[ukdane]

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]

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:
.Range("E2").Resize(LastRow - 1).FormulaR1C1 = _
"=AND(RC[-1]<>""KLAR TIL ARKIVERING"",RC[-1]<>""HUSK LUK"",RC[-1]<>""HUSK SK?N"")"


I'm using Excel 2000 and Excel 2003.

[Bob Phillips]

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



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


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



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


instead.

[ukdane]

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]

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

[ukdane]

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

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


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