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

 

[johnnyL]

@Michael M, I wondered how your code worked so quickly. Upon further review, your formulas only go down to row 55. I believe row 1501 is the desired row number, unless I have been incorrect in my approach all along. @RAJESH1960, care to chime in here?

 

[Michael M]

@johnnyL
Did you modify the offending line and rerun the code ??

 

[RAJESH1960]

Try this on a SAMPLE of your workbook....I did it on the tablet, so it may not be exactly what you wanted....

Sub ClearData()
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:K" & Cells(Rows.Count, "A").End(xlUp).Row)
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
End Sub

Your code took just 2 seconds to complete. But it has copied the formula from Formulas sheet to Sheet B up to 55th row only. As I mentioned this is a sample data. The original data will contain rows up to 5000 max. Only if you change the range to the end of the sheet, it would be awesome.

 

[Michael M]

I'm not able to help ATM.. busy with something else.. I can have a look tomorrow if you can tell me which sheet we're talking about

 

[RAJESH1960]

I'm not able to help ATM.. busy with something else.. I can have a look tomorrow if you can tell me which sheet we're talking about

Wokay!!

 

[Michael M]

So..can you give me more info ??

 

[Michael M]

If the range of cells that require a formula is driven by the GetData code..why not put the required line of code in that macro rather than the Clearwater macro

 

[Michael M]

If the range of cells that require a formula is driven by the GetData code..why not put the required line of code in that macro rather than the cleardata macro

 

[RAJESH1960]

If the range of cells that require a formula is driven by the GetData code..why not put the required line of code in that macro rather than the Clearwater macro

Did you view each formula? They are too complicated for me to write a code. Each cell has a different formula from K to BD and they are filled down to the last row.

Shared Test.xlsm
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
AB
AC
AD
AE
AF
AG
AH
AI
AJ
AK
AL
AM
AN
AO
AP
AQ
AR
AS
AT
AU
AV
AW
AX
AY
AZ
BA
BB
BC
BD
3
 
 
0
 
 
0
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
B
Cell Formulas
Range
Formula
K3
K3
=IFERROR(INDEX($A$3:$I$2000,MATCH($M3,$C$3:$C$2000,0),1),"")
L3
L3
=IFERROR(INDEX($A$3:$I$2000,MATCH($M3,$C$3:$C$2000,0),2),"")
M3
M3
=IFERROR(INDEX($C$3:$C$2000,MATCH(0,COUNTIF($M2:$M$2,($C$3:$C$2000)),0)),"")
N3
N3
=IF(IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS(N4:$O4)+1)/2)),4),"")="","")
O3
O3
=IF(M3="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS(O4:$O4)+1)/2)),5),""))&""
P3
P3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:P3)+1)/2)),9),""))
Q3
Q3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:Q4)+1)/2)),5),""))&""
R3
R3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:R3)+1)/2)),9),""))
S3
S3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:S4)+1)/2)),5),""))&""
T3
T3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:T3)+1)/2)),9),""))
U3
U3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:U4)+1)/2)),5),""))&""
V3
V3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:V3)+1)/2)),9),""))
W3
W3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:W4)+1)/2)),5),""))&""
X3
X3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:X3)+1)/2)),9),""))
Y3
Y3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:Y4)+1)/2)),5),""))&""
Z3
Z3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:Z3)+1)/2)),9),""))
AA3
AA3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AA4)+1)/2)),5),""))&""
AB3
AB3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AB3)+1)/2)),9),""))
AC3
AC3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AC4)+1)/2)),5),""))&""
AD3
AD3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AD3)+1)/2)),9),""))
AE3
AE3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AE4)+1)/2)),5),""))&""
AF3
AF3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AF3)+1)/2)),9),""))
AG3
AG3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AG4)+1)/2)),5),""))&""
AH3
AH3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AH3)+1)/2)),9),""))
AI3
AI3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AI4)+1)/2)),5),""))&""
AJ3
AJ3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AJ3)+1)/2)),9),""))
AK3
AK3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AK4)+1)/2)),5),""))&""
AL3
AL3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AL3)+1)/2)),9),""))
AM3
AM3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AM4)+1)/2)),5),""))&""
AN3
AN3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AN3)+1)/2)),9),""))
AO3
AO3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AO4)+1)/2)),5),""))&""
AP3
AP3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AP3)+1)/2)),9),""))
AQ3
AQ3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AQ4)+1)/2)),5),""))&""
AR3
AR3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AR3)+1)/2)),9),""))
AS3
AS3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AS4)+1)/2)),5),""))&""
AT3
AT3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AT3)+1)/2)),9),""))
AU3
AU3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AU4)+1)/2)),5),""))&""
AV3
AV3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AV3)+1)/2)),9),""))
AW3
AW3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AW4)+1)/2)),5),""))&""
AX3
AX3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AX3)+1)/2)),9),""))
AY3
AY3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AY4)+1)/2)),5),""))&""
AZ3
AZ3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AZ3)+1)/2)),9),""))
BA3
BA3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:BA4)+1)/2)),5),""))&""
BB3
BB3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:BB3)+1)/2)),9),""))
BC3
BC3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:BC4)+1)/2)),5),""))&""
BD3
BD3
=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:BD3)+1)/2)),9),""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RAJESH1960]

So..can you give me more info ??

Tell me what more information do you need and regarding which macro - get data or clear data