Error 1004: AutoFilter method of Range class failed

[vaibhavdxt]

I am using same code in three different functions and it's working fine with two of them but with third one it's giving me "Error 1004: AutoFilter method of Range class failed".

I have a long code and I am getting error in this part of the code: -

Code:-

Public Sub XYZ (fcstartdate As Long, finaldate As Long)

With Sheets("XYZ")
.Range("$A$1:$AB$860423").AutoFilter Field:=14, Operator:= _
xlFilterValues, Criteria1:=">=" & fcstartdate, Operator:=xlAnd, Criteria2:="<=" & finaldate

Selection.SpecialCells(xlCellTypeVisible).Select
End With

End Sub

Please tell me what I should do to correct it.

I have checked that sheet is NOT protected and there is no merged rows. I also checked the number of rows in the sheet by recording macro which tells me there are 860423 though I see 67423 rows only.

Also when I ran the code with both figures (860423 and 67423), it gave me the same error.

Thanks in advance.

Regards

 

[Sektor]

You use Longs, while you must use dates as strings. When using Autofilter, dates must be in U.S. format, i.e. the first comes month, then - day.

 

[Norie]

How are you calling the code?

 

[vaibhavdxt]

I am sorry for my mistake. I forgot to add part of the code
I have a long code and I am getting error in this part of the code: -

Code:-

Public Sub XYZ (fcstartdate As Long, finaldate As Long)

'To show that my function is triggering properly.

MsgBox ("I am inside XYZ")


' Changing the column dates in general format so that I can filter them with numeric format.


Sheets("XYZ").Select
Range("N:N").Select
Selection.NumberFormat = "General"





With Sheets("XYZ")
.Range("$A$1:$AB$860423").AutoFilter Field:=14, Operator:= _
xlFilterValues, Criteria1:=">=" & fcstartdate, Operator:=xlAnd, Criteria2:="<=" & finaldate

Selection.SpecialCells(xlCellTypeVisible).Select
End With

End Sub

Please tell me what I should do to correct it.

 

[Sektor]

Did you read my answer?

 

[vaibhavdxt]

Yes Sector, I did. Please note that I am using US format dates. First month, then date and year. The same code is running for two other functions properly.

 

[Sektor]

Your fcstartdate variable is even not a String, but Long. Yes, Excel internally stores dates as numbers, but AutoFilter requries String variable with U.S. formatted date.

 

[vaibhavdxt]

Please read my comment on code below.

Public Sub XYZ (fcstartdate As Long, finaldate As Long)

'To show that my function is triggering properly.

MsgBox ("I am inside XYZ")


' Changing the column that contain dates in sheet in general format so that I can filter them with numeric format.


Sheets("XYZ").Select
Range("N:N").Select
Selection.NumberFormat = "General"


' Here I am using the filter in the same column which has dates in general numeric format NOT as string. Filter is on field 14 or 'N'


With Sheets("XYZ")
.Range("$A$1:$AB$860423").AutoFilter Field:=14, Operator:= _
xlFilterValues, Criteria1:=">=" & fcstartdate, Operator:=xlAnd, Criteria2:="<=" & finaldate

Selection.SpecialCells(xlCellTypeVisible).Select
End With

End Sub

 

[MARK858]

What Sektor is telling you is that the autofilter requires it to be a string (it doesn't matter what it is in the cell, you are dealing with VBA).
Are your normal window setting mm/dd/yyyy?

Having said all that what happens with

With Sheets("XYZ")
.Range("$A$1:$AB$860423").AutoFilter Field:=14, Operator:= _
xlFilterValues, Criteria1:=">=" & Clng(fcstartdate), Operator:=xlAnd, Criteria2:="<=" & Clng(finaldate)

Selection.SpecialCells(xlCellTypeVisible).Select
End With

 

[vaibhavdxt]

By call function.



Dim datestr1 As String
Dim datestr2 As String

Dim fcstartdate As Long
Dim fcenddate As Long
Dim fcondate As Long
Dim finaldate As Long
Dim fulldate As Long






datestr1 = TextBox1.Text
fcstartdate = CLng(DateValue(datestr1))
datestr2 = TextBox2.Text
fcenddate = CLng(DateValue(datestr2))
fulldate = fcstartdate + 0.9999
finaldate = fcenddate + 1.25



If OptionButton1.Value = True Then
Call XYZ(fcstartdate, finaldate)
ElseIf (OptionButton2.Value = True) Then
Call ABC(fcstartdate, finaldate)
ElseIf (OptionButton3.Value = True) Then
Call EFG(fcstartdate, finaldate)
ElseIf (OptionButton4.Value = True) Then
'Call IJK(fcstartdate, finaldate)
Else
MsgBox ("Please choose one of the options")
End If







End Sub