VBA :Inconsistency filtering with autofilter using referenced values from ComboBoxes

[QIP]

Hi all,

First, I'd like to thank this forum of basically helping me learn VBA, without any programming experience, and without asking questions. basically the wealth the forum has of existing questions and solutions are enough probably to solve all issues.

Yet i can's solve this one by myself

I have "written" a code, that filters a range based on combobox values found in another sheet, then copies the result and does some basic calculations.

the code is run when clicking a cell (Report in G1):

This is the code from the Report sheet (stored in a sheet):

Please Login or Register  to view this content.

The code works! yet not everytime! sometimes it doesn't apply the filters and copies the whole table. at first this wasnt an issue, as the data were small, but now the data table has expanded to the point that copying the whole data takes a lot of time and make Excel hangs.

when it doesnt work, I have a Clear results VBA code that clears the pasted table, Clear (Clear , G2), after which i click report again and hope that it works

The autofilter picks up values from two comboboxes, found in Report sheet, the entries in both combo boxes are entered via two macros, called when the Report sheet is activated (above, in red):

This is the code for the Report (stored in a module):

Please Login or Register  to view this content.

I tried stepping into/debugging the code, when i do that, autofilters work all the time.

I'd appreciate any help here, using Excel 2007, PC, fully updated.

Thanks
Ahmed

[kev_]

Try replacing:

Please Login or Register  to view this content.

with:

Please Login or Register  to view this content.

[kev_]

or replacing

Please Login or Register  to view this content.

with:

Please Login or Register  to view this content.

[QIP]

Hikev_

thanks for the input.

I tried both, they didnt work:

first option (on error resume next) results in the same behavior

second option (.AutoFilterMode = False) gives an error popup (run-time error 438: object doesn't support property or method)

do you think the issue is related to auto filter?

from observation, first time running the report macro (after opening the workbook) always fails. second time will work! that is, without doing anything: aka changing values in the comboboxes or switching sheets, etc.

do you think thats relevant?

Ahmed

[Arkadi]

autofiltermode is a property of the sheet not the range AFAIK, so :

Please Login or Register  to view this content.

should work, this would go just before you apply the new filter.

[QIP]

HI Arkadi,

I added that line to remove autofilter, but that doesnt really solve the issue,. The first time I do the report the whole data is inserted (autofilter is not applied),
doing the exact thing again, it works perfectly!

Thanks
Ahmed

[Arkadi]

any chance you can provide the workbook with sensitive data cleaned out?

[QIP]

HI,

attached

I tried to clean everything, fingers crossed

PS: all formulas will not work, thats fine

to reproduce:

go to Report sheet, click report (it might take a while)

you will notice it will not take whatever data are there in the combo boxes.

after its done, click clear or click report again (clear is part of report) and data will be filtered via input from the comboboxes and copied correctly

PS: some bits in the code are either 'ed or redundant

Thanks in advance.

Regards,
Ahmed

[Arkadi]

I noticed I can make it fail every time by switching to the 2017 sheet and then back again, run the code, it fails, run it again it works, switch sheets, it fails.

However, by modifying one line (adding specialcells) in the filter/copy code, it seems to work consistently, maybe the filter is too slow in registering, though it clearly works since the specialcells statement would only help if the filter is applied properly:

Please Login or Register  to view this content.

EDIT: Forgot to specify.... this is in the FilterAndCopy Subroutine.

[QIP]

Hi Arkadi,

Thank you very much! that worked! i have the least idea what specialcells are (will start reading about them).

thanks for the efforts and time.

Ahmed