Loop using Autofilter stops randomly after some successful iterations

[Sendrik]

Hello everybody,

I'm quite new to VBA and have now encountered a problem which I couldn't google my way out of. Maybe someone here as an idea.

So my goal is the following:
I have a worksheet with data in it. For every column of data, I want to calculate the average of the Bottom 25% / Top 25% of data entries. To get to the Bottom/Top25%, I use Autofilter. I created another sheet, which has the same column numbers/headers as the 'data sheet'. In the end I want it to filter for different regions, which is why I created a double loop to cycle through the various columns / rows.

Now the problem:
Sometimes this works fine for a couple of iterations. Then it stops giving me various runtime 1004 errors.
Sometimes it will not start at all, also giving me a runtime 1004 error (but then it's always 'application defined or object-defined error')
So the weird thing is that it gives me the right results, but then just stops.

Things I've tried:
- Changing security settings
- Application.ScreenUpdating = False
- Removing all relative cell references where possible; the data set will change frequently, so I cannot use absolute references for the size of my dataset

Here is my code:

Sub HighLow()
'
Dim LastCell As Range


Application.ScreenUpdating = False


' Define size of dataset


    lastrow = Sheets("Adjusted Data").Cells(Rows.Count, 1).End(xlUp).Row
    lastcolumn = Sheets("Adjusted Data").Cells(1, Columns.Count).End(xlToLeft).Column
    Set LastCell = Cells(lastrow, lastcolumn)


' Delete Autofilter & Set new Autofilter
    Sheets("Adjusted Data").AutoFilterMode = False


' Set new Autofilter and start loop
    For i = 2 To 16 Step 3
        For j = 4 To lastcolumn Step 1
        Sheets("Adjusted Data").Range("A1", LastCell).Autofilter Field:=j, Criteria1:=25, Operator:=xlBottom10Percent


' Calculate Average
        Sheets("Sheet1").Cells(i, j).Value = Application.WorksheetFunction.Average(Range(Cells(1, j), Cells(Rows.Count, j).End(xlUp)).SpecialCells(xlCellTypeVisible))
    'Delete Autofilter & Set new Autofilter
    Sheets("Adjusted Data").AutoFilterMode = False
        Next j
    Next i


Application.ScreenUpdating = True


End Sub

The line that gives the error:

Sheets("Adjusted Data").Range("A1", LastCell).Autofilter Field:=j, Criteria1:=25, Operator:=xlBottom10Percent

I have no idea why this occurs and why it works sometimes for some iterations. It would be great if you could help me out here.

Many thanks,
Sendrik

 

[Sendrik]

Hi again,

just so I know and can adapt in the future: Was this a really stupid question or does the post lack sth. in form, like to few information? Or does genuinely nobody know an answer to this?
It would be great if someone of the regulars could clarify. That would help me to better phrase/design my questions in the future.

Kind regards,
Sendrik