VBA AutoFilter does not Work in run mode, only in debug mode

Hi everyone!

I have had such a headache with this. I have a very long VBA code that works with around 7 workbooks, it goes perfectly well, until I try to filter one of the sheets using a command I use constantly:

Function CriarArquivos(operation As String, Concessionaria As String, PlanilhaBase As String) As Boolean
Dim countLines As Long
Dim countFiltro As Integer

'Activate the correct Workbook and Sheet
Workbooks(PlanilhaBase).Sheets(1).Activate
'countLines gets the total number of rows with data in the file
countLines = Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count

Range("A1:J1").Select

' Filter by Concessionaria
ActiveSheet.Range("$A$1:$J" & countLines).AutoFilter Field:=5, Criteria1:="" & Concessionaria

' Defines countFiltro as the total number of rows for the filtered list
countFiltro = ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count

' Start the code if at least one item was found.
If countFiltro > 1 Then

The variables:
countFiltro which should return the number of rows filtered by the Autofilter but it is returning always 1

The Activesheet, after the Macro has run, does not seem to be filtered at all

Funny thing is that it works perfectly if I go into debug mode and keep F8 pressed.

I am trying to debug this using Debug.Print. The conditional statement gets always FALSE in this way.

I have turned internet upside down to find solutions, I have already tried to:
Add DoEvents in case it was a processing issue,
Tried to copy the whole code and data to another file in case the original one was corrupt.
Removed all the WITH statements from the whole code, since it has been an issue found online...
Restarted Excel
Restarted the Computer

Nothing works...

Any thoughts ?

Thanks for the answer Keebellah,

I did try that ... it did not work. =/

Hit The_Prist,

Thanks very much, I tried as suggested, but now the variables countLines and countFilter are not retrieving the expected values...

countLines is always returning 98, not sure why.
countFiltro still returning 1

Any ideas ?

Regards,

Aganove

Thanks for the lesson Keeellah

Please Login or Register  to view this content.

Please find attached the files.

Test.xlsmTest2.xlsx

The Prist:

The way the system is designed, I there is a central sheet that triggers some standard procedures, one of these is this CriarArquivos. That's why there is this IF BLOCK>

This procedure works this way:
It filters the source, test2.xls according to the Concessionaria

It creates a new workbook and saves it to the Concessionaria name
It copies the result of the filter to this concessionaria sheet and saves it again and close it

Then it loops to another concessionaria.

Somehow it only works in debug mode...

Interesting, isn't it ? I have other filters that are kept unchanged, so I took them out of the loop for performance and that's why the sheet is already loaded with some filters applied. I had to made this sample for you, so it could fit in the forum size, but the real sheet has around 13000 rows...

I have done similar procedures many times without problem. I have also tried to add DoEvents after every sheet load and manipulation like workbook open, close, save, and filter application....