RAJESH1960
Banned for repeated rules violations
- Joined
- Mar 26, 2020
- Messages
- 2,313
- Office Version
- 2019
- Platform
- 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.
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) |