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]

My bad. I forgot to color the columns?

 

[RAJESH1960]

See you tomorrow. JohnnyL.

 

[johnnyL]

How about:

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

 

[RAJESH1960]

That worked. Only difference is delete replaced by clearcontents. Right....

 

[RAJESH1960]

Now for a peaceful sleep. Thanks johnnyL. See you soon. Good Night

 

[johnnyL]

I also corrected one of the lines you inserted. You had AB2 in the top section, it needs to be AB3 unless you want it to delete the formula line in that section.

 

[RAJESH1960]

You had AB2 in the top section, it needs to be AB3 unless y

copy pasted and didn't notice. ?