Clearing Autofilter and Advancedfilter

I am having a problem to which I cannot find a solution. I have written a program that uses AdvancedFilters to create dropdown menus for comboboxes in an Excel Form, so that duplicates are eliminated from the dropdowns (below is a sample of one of those dropdowns). I also am using AutoFilter in the same Excel Form to filter the spreadsheet.

I wrote this program to be used by multiple users with different data of the same type. All columns are consistent across the worksheet, and I delete the existing rows of data in order to paste the new rows of data onto the worksheet.

The problem is that the filtering works fine until I delete the old rows and paste the new rows. The filter will not recognize the new rows of data. It appears that the AdvancedFilter function is working fine, but the AutoFilter will not recognize the new data.

I have provided code below, but I don't know that it will help solve the problem. It seems that the programming is remembering the filters from the old data. I have tried using AutoFilterMode = False and ActiveSheet.ShowAllData, but nothing seems to reset the worksheet as a new worksheet.

AutoFilterMode = False

ActiveSheet.ShowAllData

I'm open to any and all suggestions.

Sample Code for AdvancedFilter:

Sheets("Grant Spreadsheet").Activate

Me.ARRACmboBx.SetFocus 'Set cursor on the ARRACmboBx field
Range("a1:a200").AdvancedFilter Action:=xlFilterCopy,_
CopyToRange:=Worksheets("Lists").Range("j1"), Unique:=True
'Copy Unique Values to List Worksheet for use in Dropdown

Dim List As Worksheet

Set List = Worksheets("Lists")

With List.Range(("j2:j4"),List.UsedRange.Rows(Worksheets("Lists")_
.UsedRange.Rows.Count)).Sort Key1:=List.Range("j2:j4"),_
Order1:=xlAscending, Header:=xlNo, OrderCustom:=1,_
MatchCase:=False, Orientation:=xlTopToBottom,_
DataOption1:=xlSortTextAsNumbers

End With


Code for AutoFilter:

With .Range("a1:at1000")

.AutoFilter Field:=1, Criteria1:="*" & Me.ARRACmboBx.Value & "*"
.AutoFilter Field:=2, Criteria1:="*" & Me.PreAwardCmboBx.Value & "*"
.AutoFilter Field:=3, Criteria1:="*" & Me.PICmboBx.Value & "*"
.AutoFilter Field:=4, Criteria1:="*" & Me.ProjTypeCmboBx.Value & "*"
.AutoFilter Field:=5, Criteria1:="*" & Me.GrantNameCmboBx.Value & "*"
.AutoFilter Field:=6, Criteria1:="*" & Me.CompleteCmboBx.Value & "*"
.AutoFilter Field:=7, Criteria1:="*" & Me.OperatingCmboBx.Value & "*"
.AutoFilter Field:=8, Criteria1:="*" & Me.FundNoCmboBx.Value & "*"
.AutoFilter Field:=9, Criteria1:="*" & Me.OrgTxtBx.Value & "*"
.AutoFilter Field:=10, Criteria1:="*" & Me.ProjectTxtBx.Value & "*"
.AutoFilter Field:=11, Criteria1:="*" & Me.AccountTxtBx.Value & "*"
.AutoFilter Field:=14, Criteria1:="*" & Me.GrantAgencyCmboBx.Value & "*"
.AutoFilter Field:=15, Criteria1:="*" & Me.GrantDeptCmboBx.Value & "*"
.AutoFilter Field:=16, Criteria1:="*" & Me.ProgramCmboBx.Value & "*"
.AutoFilter Field:=17, Criteria1:="*" & Me.PassthroughCmboBx.Value & "*"
.AutoFilter Field:=18, Criteria1:="*" & Me.CatalogNoCmboBx.Value & "*"
.AutoFilter Field:=19, Criteria1:="*" & Me.GrantIDCmboBx.Value & "*"
.AutoFilter Field:=20, Criteria1:="*" & Me.FederalCmboBx.Value & "*"

End With


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.