XL2003 autofilter doesn't play niceley with the other children

obiron

Active Member
Joined
Jan 22, 2010
Messages
469
guys,

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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
I can't reproduce this. When this happens, could you tell us:
1. What is in AccountRef
2. What is in wsTR.Cells(TRrow, 7)
3. Whether all the rows are filtered out or not
4. Any completely blank rows in the autofilter range with data below them before autofilter is applied?
5. Whether there's a header in Row 1
6. An idea of the data in columns A and B

If you can use Excel Jeanie or some such to show us what the sheet looks like before and after would be really useful.

Better still if you could post a sample worksheet on the internet somewhere with it happening with some code like:
Code:
accountRef = "z01"
With ActiveSheet
    .AutoFilterMode = False
    .Range("A:N").AutoFilter field:=1, Criteria1:="001" 'wsTR.Cells(TRrow, 7)
    .Range("A:N").AutoFilter field:=2, Criteria1:=accountRef
End With
so that we can see the problem occurring.

btw, activesheet.autofilter.range is the filter range, including header (dropdown) row, so
activesheet.autofilter.range.SpecialCells(xlCellTypeVisible).rows.count
return 1 if all the rows have been filtered out.
 
Last edited:
Upvote 0
and
activesheet.autofilter.range.row+activesheet.autofilter.range.rows.count
will be the row number of the row immediately below the autofilter range.
 
Upvote 0
Thanks P45Cal

I still havn't found a cause or solution to issue 1.

I think I have worked out the cause of problem 2), The value in AccountRef is a string but Excel converts the value to a number when posting it into Column B. This causes the next filter to fail to find a match which results in no lines being shown rather than the found line and all blank lines.

Sample values for Cols A, B

Code:
[FONT=Courier New]A           B[/FONT]
[FONT=Courier New]CostCenter  Account[/FONT]
[FONT=Courier New]12168       1234[/FONT]
[FONT=Courier New]12169       1234[/FONT]
[FONT=Courier New]12170       1234[/FONT]
wsTR.Cells(TRrow, 7) = "12170"
AccountRef = "01234" (note the leading zero - cell that populates accountRef is "'01234"

When the filter is applied, this causes no rows to be displayed except the heading row.

I have modified the code
Code:
With wsBud
    .AutoFilterMode = False
    .Range("A:N").AutoFilter field:=1, Criteria1:=wsTR.Cells(TRrow, 7)
    If isNumeric(AccountRef) Then    
        .Range("A:N").AutoFilter field:=2, Criteria1:=Cint(AccountRef)
    Else
        .Range("A:N").AutoFilter field:=2, Criteria1:=AccountRef
    End If   
End With
 
If wsBud.UsedRange.SpecialCells(xlCellTypeVisible).End(xlDown).Row = 65536 Then

and this seems to have fixed the problem.
 
Upvote 0
I don't think so.

Occassionally when selecting columns A:N I get the drop down arrows but nothing to drop down. setting autofiltermode = false and reselecting in VBA does not correct the error but manually clearing the autofilter option and higlighting cols A-N and then choosing autofilter does. the error occurs in VBA even after clearing all cells except the header row.

admittedly the error only occurred AFTER error 2 so I should not get it anymore, but it appears to be inconsistent behaviour AND I can't find a way to correct it using VBA
 
Upvote 0

Forum statistics

Threads
1,223,493
Messages
6,172,618
Members
452,466
Latest member
Lynlindsay

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