Speeding up my loop

  • I cannot make this run any quicker. The data concerned covers a filtered range 56 columns wide and between 3 and 50 rows long. However it seems to be the number of columns that create the lag and it takes just under 3 seconds to complete. Any ideas?

    1. application.screenupdating = false
    2. Set medianrange = Range(Cells(7, 3), Cells(LastRow, LastCol)) ' last row and last column set
    3. On Error Resume Next 'The column may have no data
    4. For Each col In medianrange.Columns
    5. Cells(4, col.Column) = Application.WorksheetFunction.Median(col.SpecialCells(xlCellTypeVisible))
    6. Next
    7. Application.ScreenUpdating = True

    Thanks in advance


  • Re: Speeding up my loop

    I ran your sample code using a range of 57 rows and 250 columns. Each cell filled with random numbers from 0 to 1,000,000. It ran almost instantaneously.

    It most have someting to do with the size of your workbook data and/or program size.


    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

  • Re: Speeding up my loop

    In addition to what Bill has mentioned you may want to look at setting the calculation to manual prior to running the code, If calculation is set to automatic that might cause some lag.

  • Re: Speeding up my loop


    Many thanks for your quick replies. It was partly the auto calculation but also the fact that I was running an autochart updating on the selection_change event (oops - forgot to mention). I've managed to get it down to a few shuddering milliseconds turning calc to manual and disabling the selection change while executing the median-calculation sub.


    Great forum!

    Thanks again