File Error when opening...due to sorting

mharper90

Board Regular
Joined
May 28, 2013
Messages
117
Office Version
  1. 365
Platform
  1. MacOS
I'm not sure if this error is specific to the LAN at work, or if it's a general excel problem, but anytime I run a macro that involves Autofilter, when I save it and reopen it, I get a warning that the file has errors and must be recovered. The recovery log generated says that it is removing the following records: "Sorting from /xl/worksheets/sheet2/xml part" and the same for sheet3.

The only common denominator I've found is my Autofilter macro. If I don't run that macro after recovering the file, then it doesn't give me the error next time I open it. Here is the macros that control this Autofiltering.

Code:
UserForm 10
Option Explicit
Private Sub CommandButton1_Click()

Dim ws1     As Worksheet: Set ws1 = ThisWorkbook.Sheets("Main Data")

Me.Hide

Application.ScreenUpdating = False

ws1.Rows.Hidden = False

     ws1.Range("A7").AutoFilter Field:=20, Criteria1:="=", Operator:=xlOr, Criteria2:="INACTIVE P" & Me.ComboBox1.Value, VisibleDropDown:=False
     ws1.Range("A7").AutoFilter Field:=18, VisibleDropDown:=False
     ws1.Range("A7").AutoFilter Field:=19, VisibleDropDown:=False
     ws1.Range("A7").AutoFilter Field:=21, VisibleDropDown:=False

Application.ScreenUpdating = True

shwFrm11

End Sub



Code:
UserForm11
Private Sub CommandButton1_Click()

Dim ws1     As Worksheet: Set ws1 = ThisWorkbook.Sheets("Main Data")

Application.ScreenUpdating = False

ws1.AutoFilterMode = False

Application.ScreenUpdating = True

Me.Hide

End Sub


Is there a better way to completely stop the "sorting" other than ws1.AutoFilterMode = False, so that this annoying error goes away?

Also, while these macros are here...any idea why columns 18, 19, and 21 had the dropdown arrows on them when I applied the Autofilter in the first macro? I only have those 3 lines of code in there because the drop down arrows just showed up when I applied the field to column 20.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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
Back
Top