Reduce the time of running the macro

[RAJESH1960]

Hello Experts

I have this file which runs through a long process to get the final result. I have recorded several macros along with the help of macros I got through this board. The problem is it takes at least 2 minutes to get the result. I am not able to figure out why the macro is running so slow. With your expert advice, I am sure I will be able to edit and run the macro in just a few seconds. I get an error when I try to edit the multiple select options in the code

The sheet “Original” is the raw data pasted. “SheetF” is the final result of the data. To get the result the macro runs through different sheets to get the expected result. “GetData” is the macro to get the result and “ClearData” is to clear the old data so that a new data can be pasted. “ClearData” also inserts once again, different formulas in the “SheetB” which were deleted while getting the data. In short, I am arranging the data of Original sheet in the format as shown in SheetF with this code.
I am sharing the link of a sample file.

Test.xlsm

drive.google.com

 

[RAJESH1960]

The array formula, 3rd formula if I recall correctly, is the bottleneck.

Yes. I am posting a new query. If possible please try it since you know what I am trying to do.
Need to replace the formulas with code

 

[RAJESH1960]

 

[RAJESH1960]

I'm guessing you want this line to extend to "5000" rows ??

Sheets("Formulas").Range("B2:AU2").Copy Sheets("B").Range("K3:K" & Cells(Rows.Count, "A").End(xlUp).Row)

If so, change to

Sheets("Formulas").Range("B2:AU2").Copy Sheets("B").Range("K3:K5000")

But I don't see the point of doing this if the GetData is only 50, 100, 500 rows ??

Micheal M Thank you for your help and time.

 

[Michael M]

Glad to provide some input..??

 

[RAJESH1960]

Further, if you use this code as your clear data macro it reduces the run time by over 60%

Sub ClearData2()
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
Sheets("Bank").UsedRange.Clear
Sheets("A").UsedRange.Clear
Sheets("B").Range("A3:BD" & Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
Sheets("E").UsedRange.Clear
Sheets("Z").UsedRange.Clear
Sheets("F").Range("B2:BE" & Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
Sheets("Formulas").Range("I3").Copy Sheets("B").Range("I3:I" & Cells(Rows.Count, "A").End(xlUp).Row)
Sheets("Formulas").Range("B2:AU2").Copy Sheets("B").Range("K3:K5000")
Sheets("B").Range("K3:K" & Cells(Rows.Count, "A").End(xlUp).Row).Borders.LineStyle = xlLineStyleNone
Sheets("B").Range("I3:I" & Cells(Rows.Count, "A").End(xlUp).Row).Borders.LineStyle = xlLineStyleNone
Sheets("Original").Activate
Range("A1").Select
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

Michael M. I removed the formula sheet after JohnnyL helped me to understand. Hence I removed the 4 lines from your code, 2 lines of Sheet formulas and 2 lines of sheet B. Now it is good. It takes a second only to run the code. The clearData is solved man. Thanks a lot.? Hoping to solve the GetData also in a few seconds. ?

 

[johnnyL]

@RAJESH1960 In regards to sheet 'B', Do you want that zero at the end of Column I and the 2 rows of zeroes at the end of the K3:BD range?

 

[RAJESH1960]

@RAJESH1960 In regards to sheet 'B', Do you want that zero at the end of Column I and the 2 rows of zeroes at the end of the K3:BD range?

No not at all. I tried to remove them by adding &"" in the end of each formula but I was not able to do in a few columns.

 

[johnnyL]

Ok, I'll look into that.

 

[RAJESH1960]

Time to hit the sack and grab a few hours sleep. See you tomorrow. I mean afternoon. It is 5 AM now. ?

 

[RAJESH1960]

Hello JohnnyL. Are you still working on to fill the cells from K:BD..?