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

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBI
1LineDate1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859
2101-11-2021 2047.57    2047.57   102.38Name7FB205921500.0533AFKPA4886K1Z72047.572047.5711/13, VCKN Lxxyout,, By-Pxxss Roxxd, Sivxxnxxndxx Colony, Coimbxxtore-541012, MOb:9842244803, T.N11/13VCKN Lxxyout By-Pxxss RoxxdSivxxnxxndxx ColonyCoimbxxtore-541012MOb:9842244803T.N  11/13VCKN LxxyoutBy-Pxxss RoxxdSivxxnxxndxx ColonyCoimbxxtore-541012MOb:9842244803T.N
3202-11-202167.9867.98Name8FB20602855-0.032719.07No.71, xxnnxxpoorneswxxri Towers,, Shop No.3, Ibrxxhim Sxxhib Street,, Bxxngxxlore
4303-11-202122.522.5Name1FB20619459002nd Mxxin Roxxd, Mohxxnkumxxr Nxxgxxr, Opp JP Pxxrk, Bxxngxxlore- 560054
5404-11-2021164.62164.62Name3FB20626914-0.056584.81
6505-11-2021241241Name2FB2063101220.1629AWFPK0315B1Z39639.84Brodwxxy, Hubli-20, Mob:9742024274
7606-11-202112.2112.21Name3FB20645130.02542.85-54.29
8707-11-202164.3764.37Name9FB206527040.442860.91-286.09
9808-11-202199.7799.77Name12FB20664190-0.013990.47
10909-11-2021459.72459.72Name9FB2067193090.2519357.17-967.86
111010-11-2021290.48290.48Name5FB20681220029AAMFP2960L1ZL11619.04#610 RENUKxx COMPLEX, NExxR JxxLxxHxxLLY CROSS, T DxxSSxxRHxxLLI, BxxNGxxLORE, TIN # 29890604851
121111-11-2021144.11144.11Name9FB206960530.227205.7-1441.14
131212-11-202124.2824.28Name3FB20701020971.44
141313-11-202174.6474.64Name3FB20713135-0.012985.73
CopyData
Cell Formulas
RangeFormula
C2C2=IF(AM2="","",AO2)
D2D2=IF(AN2="","",AO2)
E2:L2E2=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)))),""))))))))
AO2AO2=IF(AM2+AN2=0,"",AM2+AN2+AP2+AQ2+AR2+AS2+AT2)
AV2:BE2AV2=TRIM(MID(SUBSTITUTE($AU2,",",REPT(" ",100)),COLUMNS($AV2:AV2)*100-99,100))
BF2BF2=AV2&AW2
BG2BG2=AY2&AX2
BH2BH2=AZ2&BA2
BI2BI2=BB2&BC2
A2A2=IF(B2="","",1)
A3:A14A3=IF(B3="","",A2+1)
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
How about:

VBA Code:
    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
 
Upvote 0
How about:

VBA Code:
    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.
 
Upvote 0
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
 
Upvote 0
I just included a range you had missed for delete and fill down
Range("AB2:AH" & LastRowColumnB).Delete
Range("AB2:AH" & LastRowColumnB).FillDown
 
Upvote 0
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.
 
Upvote 0
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. :rolleyes:
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top