AutoFilter FAILS - Latest office update messed up my code... what should one do?!

Sabotage

Board Regular
Joined
Sep 19, 2013
Messages
55
Dear all,
Yesterday I have updated Office-365 one of our company tablets (running the latest Windows 10 OS). In one of my data collecting excel workbooks I use userforms to gather data from users. The following line was working just fine before the update, but now I get a:

Run-time error '1004'
AutoFilter method of Range class failed

Here is the line which gives me the headache:
(RD is worksheet, cb_DatePicker is a date to be filter by)

VBA Code:
        With RD
             .Range("A2:F2").AutoFilter Field:=2, Operator:= _
                xlFilterValues, Criteria2:=Array(2, Format(cb_DatePicker, "mm/dd/yyyy"))[/COLOR]

            'rest of the code here
        End With

I have a virtual machine on the same PC with Office 2016 install and the code works as just fine. Any thoughts?
Thanks in advance...
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Where is Criteria1?
 

Sabotage

Board Regular
Joined
Sep 19, 2013
Messages
55
Where is Criteria1?
Hi Mark,
There is no Criteria1. Or at least this was my original code which is still working on the Excel 2016 version. In the criteria column the entries are all dates. Is Criteria1 a MUST?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What happens with
VBA Code:
RD.Range("A2:F2").AutoFilter 2, Array(2, Format(cb_DatePicker, "mm/dd/yyyy"))
and what happens if you put the date in manually rather cb_DatePicker?
 

Sabotage

Board Regular
Joined
Sep 19, 2013
Messages
55

ADVERTISEMENT

Same thing happens. even if I hard code any existing date in the column of dates. I went through all possibilities to my knowledge but nothing. Just tested on yet another PC (laptop) with an Office 365 installed, but this one hasn't been updated for a few months now, as the auto updates are turned off. Is it possible at all, that MS have changed something significant in the background?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows
You're code works happily for me.
Check that the cb_DatePicker is returning a valid date.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

I doubt that they have changed anything in the VBA as they don't touch it often these days. I just tried the code below on my version of 365 which is the insider version and up to date and works correctly for me (please note that my dates are in dd/mm/yyyy).

VBA Code:
Sub xxx()
Dim RD As Worksheet
Set RD = Sheet4
RD.Range("A2:F2").AutoFilter 2, Array(2, Format("05/10/2020", "dd/mm/yyyy"))
End Sub
 

Sabotage

Board Regular
Joined
Sep 19, 2013
Messages
55
I will try to revert back to an older version tomorrow on the same tablet in the same environment and will see. Implementing your latest post and altering my original code will only make a change to filter the data to nothing (no return for the dates, yet there is data for the date in the combo box). I will get back to you as soon as I test this theory with the updates. Thank you and have a wonderful day...
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Implementing your latest post and altering my original code will only make a change to filter the data to nothing
My latest post was only for testing on my computer. The only change to what was originally posted was the date format to match my regional settings and the non use of the With statement, which was serving no purpose as you only use RD once in the code.

There is nothing in your code that I can see that would produce the error other than as fluff has stated
Check that the cb_DatePicker is returning a valid date
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,328
Members
414,444
Latest member
lionking15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top