Vba code is taking too much time to do job

Re: Vba code is taking too much time to do job

Judging from the name of your procedure I assume you are copy/pasting formulas. This means that all formulas calculate every time a copy/paste occurs.

You can speed up your code by putting

Application.Calculation = xlCalculationManual

at the start of your code, and

Application.Calculation = xlCalculationAutomatic

at the end.

Re: Vba code is taking too much time to do job

Sub OLV_FORMULA_COPY()
    Dim i As Long, j As Long, count As Long
    i = 47
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    On Error GoTo errhndlr
    With Sheets("OL V")
        .Activate
        count = Application.CountIf(.Range("W4:W53"), ">0") - 1
        For j = 1 To count
            .[A4:R46].Copy .Range("A" & i)
            i = i + 43
        Next j
    End With
ErrHandl:
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
     
End Sub

Re: Vba code is taking too much time to do job

I would determine the current setting for Calculation so that it will reset to the same

Dim    lCalc as long
   lCalc = Application.Calculation


'' your code here"


Application.Calculation=   lCalc

The error above is because the error handler is not spelled the same

Option Explicit


Sub OLV_FORMULA_COPY()
    Dim i As Long, j As Long, count As Long, lCalc As Long
    i = 47


    With Application
        .ScreenUpdating = False
        lCalc = .Calculation
        .Calculation = xlCalculationManual


    On Error GoTo ErrHandlr
    With Sheets("OL V")
        .Activate
        count = Application.CountIf(.Range("W4:W53"), ">0") - 1
        For j = 1 To count
            .[A4:R46].Copy .Range("A" & i)
            i = i + 43
        Next j
    End With
ErrHandlr:


    .ScreenUpdating = True
    .Calculation = lCalc
End With


End Sub

Re: Vba code is taking too much time to do job

A simple typo which should be well within the capabilities of anyone who professes to 'program' VBA to resolve.

On Error Goto errhndl

Re: Vba code is taking too much time to do job

Don't use a Loop! Use AutoFilter for non blanks then copy the resulting filtered range.

Attach an example workbook to see if this is feasible.

Re: Vba code is taking too much time to do job

There is no reason or excuse for being sarcastic when replying to posts.

Re: Vba code is taking too much time to do job

Quote

There is no reason

Rather than derail the topic: http://www.ozgrid.com/forum/sh…02122&p=782295#post782295

Re: Vba code is taking too much time to do job

You were asked to attach a sample workbook.

There may well be a faster method, but that depends on the formatting of your sheet and the formulas you are using.

Re: Vba code is taking too much time to do job

You can attach a file of 1200KB, the limit has been increased.

If you do a sample workbook ensure that sheet structure, formatting and formulas remain the same.

Re: Vba code is taking too much time to do job

Pleased it now works, it had to be the repeated calculating that was causing each copy/paste to be slow.

Sorry about the typo in my code, did you not read the posts by others after my post that gave you the reason for the error?

The modification of my code by RoyUK in Post #6 is the one you should use.