Replace formulas with a code from row3, resize and fill down

[RAJESH1960]

Hello experts,

I have formulas in the yellow color cells which are dragged down to 1000 rows. This is the reason my code is very slow. I want to delete the formulas from Row 3 till the end and replace it with a code to fill down the cells by resizing the cells with column B (Date). One more reason to write the code is that, I have to drag the formula rows each time when the range of the rows are more than 1000. If the formulas are copied from row 2 and filled down then the code will surely be faster, hopefully. There are 61 columns in all in the worksheet. In the beginning of the code, please erase the old data from row3 in the colored cells only and then resize it with column B.

Resoze rows and fill down formula.xlsx
	A	B	C	D	E	F	G	H	I	J	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	BE	BF	BG	BH	BI
1	Line	Date	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57	58	59
2	1	01-11-2021		2047.57					2047.57												102.38				Name7	FB2059									2150	0.05	33AFKPA4886K1Z7			2047.57	2047.57						11/13, VCKN Lxxyout,, By-Pxxss Roxxd, Sivxxnxxndxx Colony, Coimbxxtore-541012, MOb:9842244803, T.N	11/13	VCKN Lxxyout		By-Pxxss Roxxd	Sivxxnxxndxx Colony	Coimbxxtore-541012	MOb:9842244803	T.N			11/13VCKN Lxxyout	By-Pxxss Roxxd	Sivxxnxxndxx ColonyCoimbxxtore-541012	MOb:9842244803T.N
3	2	02-11-2021											67.98	67.98											Name8	FB2060									2855	-0.03			2719.07								No.71, xxnnxxpoorneswxxri Towers,, Shop No.3, Ibrxxhim Sxxhib Street,, Bxxngxxlore
4	3	03-11-2021											22.5	22.5											Name1	FB2061									945				900								2nd Mxxin Roxxd, Mohxxnkumxxr Nxxgxxr, Opp JP Pxxrk, Bxxngxxlore- 560054
5	4	04-11-2021											164.62	164.62											Name3	FB2062									6914	-0.05			6584.81
6	5	05-11-2021											241	241											Name2	FB2063									10122	0.16	29AWFPK0315B1Z3		9639.84								Brodwxxy, Hubli-20, Mob:9742024274
7	6	06-11-2021											12.21	12.21											Name3	FB2064									513	0.02			542.85			-54.29
8	7	07-11-2021											64.37	64.37											Name9	FB2065									2704	0.44			2860.91			-286.09
9	8	08-11-2021											99.77	99.77											Name12	FB2066									4190	-0.01			3990.47
10	9	09-11-2021											459.72	459.72											Name9	FB2067									19309	0.25			19357.17			-967.86
11	10	10-11-2021											290.48	290.48											Name5	FB2068									12200		29AAMFP2960L1ZL		11619.04								#610 RENUKxx COMPLEX, NExxR JxxLxxHxxLLY CROSS, T DxxSSxxRHxxLLI, BxxNGxxLORE, TIN # 29890604851
12	11	11-11-2021											144.11	144.11											Name9	FB2069									6053	0.22			7205.7			-1441.14
13	12	12-11-2021											24.28	24.28											Name3	FB2070									1020				971.44
14	13	13-11-2021											74.64	74.64											Name3	FB2071									3135	-0.01			2985.73
CopyData

Cell Formulas
Range	Formula
C2	C2	=IF(AM2="","",AO2)
D2	D2	=IF(AN2="","",AO2)
E2:L2	E2	=IF(COLUMN()-COLUMN($C2)=2,IF($M2="","",IF(AND($O2="",$Q2="",$S2=""),$C2,$M2/2.5*100)),IF(COLUMN()-COLUMN($C2)=3,IF($O2="","",IF(AND($M2="",$Q2="",$S2=""),$C2,IF($M2="",$O2/6*100,IF(AND($Q2="",$S2=""),$C2-IF($E2="",0,$E2),$O2/6*100)))),IF(COLUMN()-COLUMN($C2)=4,IF($Q2="","",IF(AND($M2="",$O2="",$S2=""),$C2,IF(AND($M2="",$O2=""),$Q2/9*100,IF($S2="",$C2-(IF($E2="",0,$E2)+IF($F2="",0,$F2)),$Q2/9*100)))),IF(COLUMN()-COLUMN($C2)=5,IF($S2="","",IF(AND($M2="",$O2="",$Q2=""),$C2,$C2-(IF($E2="",0,$E2)+IF($F2="",0,$F2)+IF($G2="",0,$G2)))),IF(COLUMN()-COLUMN($D2)=5,IF($U2="","",IF(AND($V2="",$W2="",$X2=""),$D2,$U2/5*100)),IF(COLUMN()-COLUMN($D2)=6,IF($V2="","",IF(AND($U2="",$W2="",$X2=""),$D2,IF($U2="",$V2/12*100,IF(AND($W2="",$X2=""),$D2-IF($I2="",0,$I2),$V2/12*100)))),IF(COLUMN()-COLUMN($D2)=7,IF($W2="","",IF(AND($U2="",$V2="",$X2=""),$D2,IF(AND($U2="",$V2=""),$W2/18*100,IF($X2="",$D2-(IF($I2="",0,$I2)+IF($J2="",0,$J2)),$W2/18*100)))),IF(COLUMN()-COLUMN($D2)=8,IF($X2="","",IF(AND($U2="",$V2="",$W2=""),$D2,$D2-(IF($I2="",0,$I2)+IF($J2="",0,$J2)+IF($K2="",0,$K2)))),""))))))))
AO2	AO2	=IF(AM2+AN2=0,"",AM2+AN2+AP2+AQ2+AR2+AS2+AT2)
AV2:BE2	AV2	=TRIM(MID(SUBSTITUTE($AU2,",",REPT(" ",100)),COLUMNS($AV2:AV2)*100-99,100))
BF2	BF2	=AV2&AW2
BG2	BG2	=AY2&AX2
BH2	BH2	=AZ2&BA2
BI2	BI2	=BB2&BC2
A2	A2	=IF(B2="","",1)
A3:A14	A3	=IF(B3="","",A2+1)

 

[RAJESH1960]

The data in the Non colored cells is generated through a code. I want to add the above code in this and run the full macro at one go.

 

[RAJESH1960]

I will try and explain once again. The non colored cells are a result of a code. The yellow cells in Row 2 display the result of the formula in each cell which is connected to the non colored cells. With the help of a code,
1. I want to erase the data in yellow cells from Row 3 till the last row.
2. When the Date column contains values in 10 rows, then the yellow cells must copy the formula from cell Row 2 and fill the cells below to 10 rows. This way the code will run faster hopefully.

 

[RAJESH1960]

I think, it will be very difficult to insert the formulas of each cell within a code, especially E2, as it is very complicated to get the perfect result. That is why I want the code to copy the formulas from Row 2 and resize it with column Date

 

[johnnyL]

How about:

    Dim LastRowColumnB  As Long
'
    LastRowColumnB = Range("B" & Rows.Count).End(xlUp).Row                          ' Get last used row of the B column
'
    Range("C3:L" & LastRowColumnB).Delete                                           ' Delete previous data
    Range("AO3:AO" & LastRowColumnB).Delete                                         ' Delete previous data
    Range("AV3:BI" & LastRowColumnB).Delete                                         ' Delete previous data
'
'
'
'
    Range("C2:L" & LastRowColumnB).FillDown                                         ' Copy formulas down
    Range("AO2:AO" & LastRowColumnB).FillDown                                       ' Copy formulas down
    Range("AV2:BI" & LastRowColumnB).FillDown                                       ' Copy formulas down

 

[RAJESH1960]

How about:

    Dim LastRowColumnB  As Long
'
    LastRowColumnB = Range("B" & Rows.Count).End(xlUp).Row                          ' Get last used row of the B column
'
    Range("C3:L" & LastRowColumnB).Delete                                           ' Delete previous data
    Range("AO3:AO" & LastRowColumnB).Delete                                         ' Delete previous data
    Range("AV3:BI" & LastRowColumnB).Delete                                         ' Delete previous data
'
'
'
'
    Range("C2:L" & LastRowColumnB).FillDown                                         ' Copy formulas down
    Range("AO2:AO" & LastRowColumnB).FillDown                                       ' Copy formulas down
    Range("AV2:BI" & LastRowColumnB).FillDown                                       ' Copy formulas down

JohnnyL. My computer just hanged. Will check and update you in sometime.

 

[RAJESH1960]

The data is gone all hay wire. Please Check the link below. I have renamed the sheet after running the code as result. You can test it with the CopyData sheet once again before making an extra copy, so that you do not lose the data.
The data of most of the columns is pasted in different columns.
Resize copy data.xlsm

 

[RAJESH1960]

I just included a range you had missed for delete and fill down
Range("AB2:AH" & LastRowColumnB).Delete
Range("AB2:AH" & LastRowColumnB).FillDown

 

[RAJESH1960]

Looks like I am pressing some wrong buttons because the laptop shuts down suddenly. I wonder why.? This is the 3rd time it happened today.

 

[johnnyL]

I just included a range you had missed for delete and fill down
Range("AB2:AH" & LastRowColumnB).Delete
Range("AB2:AH" & LastRowColumnB).FillDown

I didn't miss it. You don't show any formulas in that range, not to mention, you didn't highlight the rows in that range yellow.