Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE HELP WITH AN SPECIAL AUTOFILL
A good day !
I have in a sheet in entyre column BD , 1423 cells with array formulas ; the cells with formulas are from range BD91 to BD65536 , in every 46 row : BD91 , BD137 , BD183 , BD229 , BD275 , BD321, BD367 , BD413 , BD459 BD505, BD551 , BD597 , BD643, BD689 , BD735 , BD781 , BD827 , BD873 BD919 , BD965 , BD1011 .................etc...............BD65319 , BD65365 ,BD65411 BD65457 , BD65503 With usual macro I have 1423 lines in macro to do this autofill : Range("BD91").AutoFill Destination:=Range("BD91:BD136"), Type:=xlFillDefault Range("BD137").AutoFill Destination:=Range("BD137:BD182"), Type:=xlFillDefault Range("BD183").AutoFill Destination:=Range("BD183:BD228"), Type:=xlFillDefault Range("BD229").AutoFill Destination:=Range("BD229:BD274"), Type:=xlFillDefault Range("BD275").AutoFill Destination:=Range("BD275:BD320"), Type:=xlFillDefault Range("BD321").AutoFill Destination:=Range("BD321:BD366"), Type:=xlFillDefault Range("BD367").AutoFill Destination:=Range("BD367:BD412"), Type:=xlFillDefault Range("BD413").AutoFill Destination:=Range("BD413:BD458"), Type:=xlFillDefault ........ ..........................................etc..... .................................................. ....................... Range("BD65411").AutoFill Destination:=Range("BD65411:BD65456"), Type:=xlFillDefault Range("BD65457").AutoFill Destination:=Range("BD65457:BD65502"), Type:=xlFillDefault Range("BD65503").AutoFill Destination:=Range("BD65503:BD65536"), Type:=xlFillDefault My goal is to get *THE BEST SPEED* for this autofill in this entyre column BD ; Please very much to provide me this kind of macro , which shall give me the MAXIMUM SPEED to autofilling the 1423 cells in entyre column ;(I use excel 2003 ). Thanks very much for your time |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE HELP WITH AN SPECIAL AUTOFILL
Is it maybe possible to select and make autofill all
1423 cells in the same time ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE HELP WITH AN SPECIAL AUTOFILL
Hi
I presume every 46th row has a different formula hence you can't just autofill all the cells in one go. Of course you don't need to type out all 1423 lines of code. You can use a for next loop: dim i as long for i = 91 to 65503 step 46 Cells(i, "BD").AutoFill Destination:=Cells(i, "BD").Resize(45,1) next i I'm not sure if this would meet your "Best Speed" goal though. It may have better performance if you can incorporate formula differences in the formula itself, that is using IF, ROW(), etc to achieve a single formula that can be used for the entire column. But whether this is faster depends on the formulas you are using. Good luck Fred "ytayta555" wrote in message ... A good day ! I have in a sheet in entyre column BD , 1423 cells with array formulas ; the cells with formulas are from range BD91 to BD65536 , in every 46 row : BD91 , BD137 , BD183 , BD229 , BD275 , BD321, BD367 , BD413 , BD459 BD505, BD551 , BD597 , BD643, BD689 , BD735 , BD781 , BD827 , BD873 BD919 , BD965 , BD1011 .................etc...............BD65319 , BD65365 ,BD65411 BD65457 , BD65503 With usual macro I have 1423 lines in macro to do this autofill : Range("BD91").AutoFill Destination:=Range("BD91:BD136"), Type:=xlFillDefault Range("BD137").AutoFill Destination:=Range("BD137:BD182"), Type:=xlFillDefault Range("BD183").AutoFill Destination:=Range("BD183:BD228"), Type:=xlFillDefault Range("BD229").AutoFill Destination:=Range("BD229:BD274"), Type:=xlFillDefault Range("BD275").AutoFill Destination:=Range("BD275:BD320"), Type:=xlFillDefault Range("BD321").AutoFill Destination:=Range("BD321:BD366"), Type:=xlFillDefault Range("BD367").AutoFill Destination:=Range("BD367:BD412"), Type:=xlFillDefault Range("BD413").AutoFill Destination:=Range("BD413:BD458"), Type:=xlFillDefault ........ .........................................etc...... .................................................. ...................... Range("BD65411").AutoFill Destination:=Range("BD65411:BD65456"), Type:=xlFillDefault Range("BD65457").AutoFill Destination:=Range("BD65457:BD65502"), Type:=xlFillDefault Range("BD65503").AutoFill Destination:=Range("BD65503:BD65536"), Type:=xlFillDefault My goal is to get *THE BEST SPEED* for this autofill in this entyre column BD ; Please very much to provide me this kind of macro , which shall give me the MAXIMUM SPEED to autofilling the 1423 cells in entyre column ;(I use excel 2003 ). Thanks very much for your time |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE HELP WITH AN SPECIAL AUTOFILL
Thanks so much , mr. Fred , your macro work and it is more easy
now for me with a smaller macro ! Indeed , with only changes in my formula I can get more speed ; I use an array formula : =AND(MMULT(TRANSPOSE(ROW(R1:BB45))^0;ISNUMBER(R1:B B45)*{1|0|1|0|1|0|1| 0|1|0|1|0|1|0|1|0|1|0|1|0|1|0|1|0|1|0|1|0|1|0|0|0| 0|0|0|0|0|0|0|0|0|0| 0|0|0})<=1) ,which take a longer time then usual functions ; Today I find this link , and I must work here : http://www.decisionmodels.com/optspeedj.htm Thank to mr. Harlan Grove I get the above formula : http://groups.google.ro/group/micros...2 af7d8488120 Maybe if I'll can make to work this formula ... : =AND(COUNT(MATCH(ROWS(B1:F5);TRANSPOSE({1;3;5}))= 1)) Thanks again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill until | Excel Discussion (Misc queries) | |||
Autofill | Excel Programming | |||
Autofill: Need to autofill one week block, (5) weekday only into cells. | Excel Discussion (Misc queries) | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming | |||
Q. Autofill question: Can I autofill alpha characters like I can numbers? | Excel Programming |