Thread: Speed up code

Hello all,

I have a piece of vba code that puts various formulas into cells in the second row of a spreadsheet and then copies and pastes them to all the cells bellow. Is there any other way I can speed this up as it's taking around 10-20 seconds for a few thousand rows...

Sub CONCURRENCY_Concurrency_Click()

Dim T1 As Date
T1 = Now()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False

Select Case True
    Case Range("B2").Value = vbNullString
    'DO NOTHING
    Case Else

    'Devices
    Range("K2").Value = "=CONCATENATE(B2,$H2)"
    'Package
    Range("L2").Value = "=CONCATENATE(C2,$H2)"
    'ParentPackage
    Range("M2").Value = "=CONCATENATE(D2,$H2)"
    'Users
    Range("N2").Value = "=CONCATENATE(E2,$H2)"
    'Conc. Sessions
    Range("O2").Value = "=IF(O1=""Conc. Sessions"",1,IF(H2=""S"",O1+1,O1-1))"
    'Conc. Devices
    Range("P2").Value = "=IF(P1=""Conc. Devices"",1,IF(AND(H2=""S"",K2=K1),P1,IF(AND(H2=""S"",K2<>K1,COUNTIF($K1:K$2,K2)=COUNTIF($K1:K$2,U2)),P1+1,IF(AND(H2=""S"",K2<>K1,COUNTIF($K$2:K2,K2)>1),P1,IF(AND(H2=""E"",COUNTIF($K$2:K2,K2)=COUNTIF($K$2:K2,U2)),P1-1,IF(AND(H2=""E"",COUNTIF($K$2:K2,K2)<>COUNTIF($K$2:K2,U2)),P1,0))))))"
    'Conc. Packages
    Range("Q2").Value = "=IF(Q1=""Conc. Packages"",1,IF(AND(H2=""S"",L2=L1),Q1,IF(AND(H2=""S"",L2<>L1,COUNTIF($L1:L$2,L2)=COUNTIF($L1:L$2,V2)),Q1+1,IF(AND(H2=""S"",L2<>L1,COUNTIF($L$2:L2,L2)>1),Q1,IF(AND(H2=""E"",COUNTIF($L$2:L2,L2)=COUNTIF($L$2:L2,V2)),Q1-1,IF(AND(H2=""E"",COUNTIF($L$2:L2,L2)<>COUNTIF($L$2:L2,V2)),Q1,0))))))"
    'Conc. ParentPackages
    Range("R2").Value = "=IF(R1=""Conc. Parent Packages"",1,IF(AND(H2=""S"",M2=M1),R1,IF(AND(H2=""S"",M2<>M1,COUNTIF($M1:M$2,M2)=COUNTIF($M1:M$2,W2)),R1+1,IF(AND(H2=""S"",M2<>M1,COUNTIF($M$2:M2,M2)>1),R1,IF(AND(H2=""E"",COUNTIF($M$2:M2,M2)=COUNTIF($M$2:M2,W2)),R1-1,IF(AND(H2=""E"",COUNTIF($M$2:M2,M2)<>COUNTIF($M$2:M2,W2)),R1,0))))))"
    'Conc. Users
    Range("S2").Value = "=IF(S1=""Conc. Users"",1,IF(AND(H2=""S"",N2=N1),S1,IF(AND(H2=""S"",N2<>N1,COUNTIF($N1:N$2,N2)=COUNTIF($N1:N$2,X2)),S1+1,IF(AND(H2=""S"",N2<>N1,COUNTIF($N$2:N2,N2)>1),S1,IF(AND(H2=""E"",COUNTIF($N$2:N2,N2)=COUNTIF($N$2:N2,X2)),S1-1,IF(AND(H2=""E"",COUNTIF($N$2:N2,N2)<>COUNTIF($N$2:N2,X2)),S1,0))))))"
    
    'Inverse S\E
    Range("T2").Value = "=IF(H2=""S"",""E"",""S"")"
    'Inverse Devices
    Range("U2").Value = "=CONCATENATE(B2,$T2)"
    'Inverse Packages
    Range("V2").Value = "=CONCATENATE(C2,$T2)"
    'Inverse ParentPackages
    Range("W2").Value = "=CONCATENATE(D2,$T2)"
    'Inverse Users
    Range("X2").Value = "=CONCATENATE(E2,$T2)"
    
    Range("K2:X2").Copy Destination:=Range("K2", Range("B2").End(xlDown).Offset(0, 22))
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayStatusBar = True
    
    MsgBox Format(Now() - T1, "s") & " Seconds"
End Select
End Sub

Thanks

Alex

At first sight, that would appear not unreasonable as you seem to have quite a lot of calculation to do. Your formulas for K:N, P:S and U:X appear to be the same (in R1C1 terms) for each group of columns, so you could enter them in one hit rather than a separate entry for each column, but I do not think that will make a lot of difference here.

The only thing I can think that you might try is to reorder the last few lines to

   
    with Application
      .Calculation = xlCalculationAutomatic
      .EnableEvents = True
      .DisplayStatusBar = True
      .ScreenUpdating = True
     End With

It looks like it could be made a lot faster. But, it would really help if you could post a dummy file with the formulas in place?

Did you consider :

Sub snb()
    [K1:N100] = [index(B1:E100,0,column(K1:N100)-11) & H1:H100]
    [U1:X100] = [index(B1:E100,0,column(K1:N100)-11) & if(H1:H100="S","E","S")]
End Sub

Did you try to run the sub snb did for you?