guys,
I have the following code
AccoutRef is a variable defined as a string and is derived from another worksheet.
The code is part of a module that looks to see if the criteria exist together in a single row and if not (last row is 65536) then it creates a new row in the worksheet for the combination.
most of the time this is working fine, however occassionally I will get one of two errors:
1) The first Autofilter command appears to only select row1. which causes an error to be thrown on the second autofilter command. You can identify this error because when you click on the dropdown arrows, you get no data options (i.e. no dropdown list of All, custom, blanks, nonblanks)
2) When the filters are applied, only row1 is shown (normally the filter will show the sucessful filter and any blank rows below the usedRange). When this error occurs the validation for last visible cell fails....
The only way to 'fix' this is to manually go into the sheet and select columns A:N, apply the Data->Autofilter and then Highlight the sheet and Delete all rows.
Why am I getting inconsistent behaviour
How do I prevent it from occuring
The common theme when the errors occurs appears to be the cell used to populate AccountRef is a number formatted as a string (because it has leading zeros) but even when setting it back to a number, the errors occur until I manually reset the autofilter range.
I have the following code
Code:
With wsBud
.AutoFilterMode = False
.Range("A:N").AutoFilter field:=1, Criteria1:=wsTR.Cells(TRrow, 7)
.Range("A:N").AutoFilter field:=2, Criteria1:=AccountRef
End With
If wsBud.UsedRange.SpecialCells(xlCellTypeVisible).End(xlDown).Row = 65536 Then
AccoutRef is a variable defined as a string and is derived from another worksheet.
The code is part of a module that looks to see if the criteria exist together in a single row and if not (last row is 65536) then it creates a new row in the worksheet for the combination.
most of the time this is working fine, however occassionally I will get one of two errors:
1) The first Autofilter command appears to only select row1. which causes an error to be thrown on the second autofilter command. You can identify this error because when you click on the dropdown arrows, you get no data options (i.e. no dropdown list of All, custom, blanks, nonblanks)
2) When the filters are applied, only row1 is shown (normally the filter will show the sucessful filter and any blank rows below the usedRange). When this error occurs the validation for last visible cell fails....
The only way to 'fix' this is to manually go into the sheet and select columns A:N, apply the Data->Autofilter and then Highlight the sheet and Delete all rows.
Why am I getting inconsistent behaviour
How do I prevent it from occuring
The common theme when the errors occurs appears to be the cell used to populate AccountRef is a number formatted as a string (because it has leading zeros) but even when setting it back to a number, the errors occur until I manually reset the autofilter range.