VBA Filter method of range class failed

Question

Microsoft Visual Basic

Run time error 1004

auto filter method of range class failed

Answer 1

RTE 1004 doesn't mean anything, means just it didn't work.

In most cases the error is due to your data, in rare cases your code refers to wrong cells or sheets.

Show us your file.

IMPORTANT: Zip your file!
https://support.microsoft.com/en-us/windows/zip-and-unzip-files-8d28fa72-f2f9-712f-67df-f80cf89fd4e5

Login to https://onedrive.live.com (Use the same Login ID and password as for this forum).
Click Upload in the top and choose your file.
After uploading, right click the file and choose Share.
Click Copy Link in the lower left edge (no need to enter an email).
Copy the link and paste it here.

Andreas.

Answer 2

Hi Andreas

Thank you for useful information, and Am sorry for respond to your email late due to I wasn't feeling fine. Thank you for helping me to resolve this issue with me VBA.

https://1drv.ms/u/s!AnXW1clz89tQaKOyYQFLA014Ktc?e=D6n3S5

Best regard

Dennis

Answer 3

Hi Dennis,

to track errors in an Userform it is easier if you set this option:

After that the code stops on this line if we start the form:

sp_sh.UsedRange.AutoFilter 7, ">=" & Me.txt_Start_Date.Value, xlAnd, "<=" & Me.txt_End_Date.Value

In sheet "Sale_Purchase" you have tables, you can not include a table in the Autofilter of the sheet.

Therefore the line has to be

sp_sh.Range("A:K").AutoFilter 7, ">=" & Me.txt_Start_Date.Value, xlAnd, "<=" & Me.txt_End_Date.Value

and the form is loaded.

Andreas.