Forum Home
Press F1
Thread ID: 40806	2003-12-19 01:51:00	How fast can you go? (Excel relistings)	rugila (214)	Press F1

Post ID	Timestamp	Content	User
201678	2003-12-20 22:43:00	Sorry Mike, I forgot to say. I have pointed the # printed row & # printed column variables to cells B1 and B2 rather than hard coding. If you put 55 in b1 and 9 in b2 it should work. Nothing to do with your Excel version. Im jealous, you got the latest I see :-). I'll will be looking at this again to do multiple source columns and turn these into pages. I will hard code next time so you dont have to worry about putting these numbers into cells. The lines that determine the printed pages are these.... nr = Cells(1, 2) 'nr = # rows per page in target range nc = Cells(2, 2) 'nc = # columns per page in target range So you can do away with putting the numbers in B1 & B2 if you change those to lines to read.... nr = 55 'nr = # rows per page in target range nc = 9 'nc = # columns per page in target range	parry (27)
201679	2003-12-21 22:59:00	At last PF1 is back online :-) Have I got a solution for you Mike! This solution is courtesy of Excel guru Tushar Mehta (http://www.tushar-mehta.com/). I believe this works best with exact pages so the following instructions will convert 65,340 cells in column A into 132 blocks of 495 cells (55x9) going down a total of 7260 rows. This assumes that all your data is in Column A in Sheet1. 1. In a new sheet select from cell A1 to I7260 2. While these cells are selected, click in the formula bar and enter the following formula then press Ctrl+Enter. The use of Ctrl+Enter merely copies the formula to all selected cells. =INDIRECT("'Sheet1'!R"&((COLUMN()-1)*55+MOD((ROW()-1),55)+1+(INT((ROW()-1)/55)*(55*9)))&"C1",FALSE) Don’t be concerned that this appears to be looking for column R in Sheet1 as this is not the case. The Indirect function can use R1C1 to reference a cell and this is what this is doing. See Excel help for info on this function. As you can see this uses Excels built in functionality so doesn’t require VBA and is the quickest solution. Obviously this doesn’t put in Page X of Y but this is unnecessary anyway because you can put in a page footer for this if you want. This also has the benefit of only needing to do this once so you can enter another 65,340 in column A and the results will be recalculated in the formula. If your not wanting to add new data and just need to retain the result you can always copy the result and paste special over itself using the values option so you avoid recalculation times. This step is probably unnecessary as it didn’t take long to recalculate for me.	parry (27)
201680	2003-12-21 23:47:00	Parry, It took about 8 seconds to run on my machine... How did you manage to get it down to 4 seconds? Mike.	Mike (15)
201681	2003-12-21 23:49:00	Parry, It took about 8 seconds to run on my machine... How did you manage to get it down to 4 seconds? Mike.	Mike (15)
201682	2003-12-22 05:03:00	Hi Mike, its because Im using an array which is faster at writing the data than it is from a range. Reading the data is pretty much nominal between a range and an array but writing is the key. The writing of the info from the array into the target cells is one operation where the writing in the other one is 65000 operations. You would need to know a little bit about the range object and arrays to get a better appreciation of what its doing. However I have an even better solution for you! This solution is courtesy of Excel guru Tushar Mehta (http://www.tushar-mehta.com/). I believe this works best with exact pages so the following instructions will convert 65,340 cells in column A into 132 blocks of 495 cells (55x9) going down a total of 7260 rows. This assumes that all your data is in Column A in Sheet 1. 1. In a new sheet select from cell A1 to I7260 2. While these cells are selected, click in the formula bar and enter the following formula then press Ctrl+Enter. The use of Ctrl+Enter merely copies the formula to all selected cells. =INDIRECT("'Sheet1'!R"&((COLUMN()-1)*55+MOD((ROW()-1),55)+1+(INT((ROW()-1)/55)*(55*9)))&"C1",FALSE) Don’t be concerned that this appears to be looking for column R in Sheet1 as this is not the case. The Indirect function can use R1C1 to reference a cell and this is what this is doing. See Excel help for info on this function. As you can see this uses Excels built in functionality so doesn’t require VBA and is the quickest solution. Obviously this doesn’t put in Page X of Y but this is unnecessary anyway because you can put in a page footer for this if you want. This also has the benefit of only needing to do this once so you can enter another 65,340 in column A and the results will be recalculated in the formula. If your not wanting to add new data and just need to retain the result you can always copy the result and paste special over itself using the values option so you avoid recalculation times. This step is probably unnecessary as it didn’t take long to recalculate for me. regards parry.	parry (27)
201683	2003-12-22 05:09:00	err theres Gremlins in the PCW server big time. First my post wasnt there and now its suddenly appeared. I presume you were meaning 4 secs for the VBA and not the formula? You will find the formula is about 1-2 secs from the time you press Ctrl-Enter.	parry (27)
201684	2003-12-22 06:21:00	Hi Mike, I was trying to think of an analogy to explain the speeds between Rugilas original code and the amended array version and I think I have one. Think of this as a task where I have say 100 beers on a table and I want to put them onto another table at the other end of the pub because the girls are nicer down there. ;-) In the original VBA code this is achieved by picking up the beer (a reading of data operation) and placing the beer on the other table (a writing operation). This is repeated for however many beers there are so that’s 100 times picking up the beer (100 x reading of column A) and 100 times placing the beer on the other table (100 x writing into destination cells), although these are combined in one line in the code. In the array version you are picking up the beer (reading operation), but instead of placing the beer directly onto the other table you are putting the beers into a magic crate. An array is simply a storage area (i.e. the crate) where I am reading the info from the cells and putting them into the array. Placing the values into an array is a writing operation too but its writing the data into memory which is almost instantaneous. The next operation takes the crate and turns it upside down over the other table so all the beers fall out onto the table (and miraculously none fall on the floor & there’s no spillage). This is the writing operation from the array to the destination cells. So the original code takes a value from one cell then places it into the destination cell directly repeated 65000 times. The array version takes the value from the cell then stores it in an array (repeated 65000 times) then when all the cells are in the array, places the whole lot into the destination range area – one write operation. The hard part is that the beers aren’t all mine so I need them to be placed onto the destination table in a particular order. Its Ruglia’s clever formula which enables the correct beer to go onto the correct place on the other table to avoid a bit of biffo. I hope that explains it a bit more. If you learn a bit of VBA then revisit this it should hopefully become clearer. By comparison, Tushars solution is using the power of Excel formulas to do the same thing. Using the beer analogy again I guess this is where there is a conveyer belt between the tables. Must be some trendy Auckland bar :-).	parry (27)
201685	2003-12-22 06:33:00	The way I see it is > For i = 1 To blk should read > For i = 1 To blk - 1 because I think you will find that I is starting at zero and not 1	mikebartnz (21)
201686	2003-12-22 07:00:00	> err theres Gremlins in the PCW server big time. First > my post wasnt there and now its suddenly appeared. I > presume you were meaning 4 secs for the VBA and not > the formula? You will find the formula is about 1-2 > secs from the time you press Ctrl-Enter. Hi parry - the 4 seconds I referred to was the 4 seconds you mentioned you got your code down to: >> Hi Rugila, beat this then - 4 secs!!! The best I could get on your one was 12 secs When I ran the code you posted with your 4 seconds, the best I could do was 8s. Mike.	Mike (15)
201687	2003-12-22 07:14:00	Hi again parry, > =INDIRECT("'Sheet1'!R"&((COLUMN()-1)*55+MOD((ROW()-1),55)+1+(INT( (ROW()-1)/55)*(55*9)))&"C1",FALSE) That worked brilliantly fast :) now all I need is a way to select all those rows in one go . But I think now that I'm getting a bit greedy . :D I'm hoping that might work will send me on a VBS course sometime soon, so maybe one day I'll understand this stuff . Mike .	Mike (15)
1 2 3 4