PLEASE HELP WITH AN SPECIAL AUTOFILL

[ytayta555]

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

 

[ytayta555]

Is it maybe possible to select and make autofill all
1423 cells in the same time ?

 

[Fred]

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]

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:BB45)*{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/micro...?hl=ro&lnk=gst&q=OLD+Problem#5b152af7d8488120

Maybe if I'll can make to work this formula ... :
=AND(COUNT(MATCH(ROWS(B1:F5);TRANSPOSE({1;3;5}))>=1))


Thanks again