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
201668	2003-12-19 01:51:00	Hi Parry (in particular), Are you still tuned in on this stuff? I decided to go to the basics of the matter and do a direct relocation of cells, rather the mathematical formula stuff I used a couple of days ago. So, with a listing in Col A of 1 to 65500, I mapped that into 133 blocks (easily converted to pages) of 55 rows and 9 columns each with VB macro as following : This one takes 8 seconds on my machine (unless you highlight one of the in-use cells when it takes 12) as compared to my earlier attempt which took about 9 (also takes 12 if an in-use cell is highlighted). seconds. The code in this one is more transparent so you can see what is happening (??) I hope. And it has the merit of not needing to dim any variants or strings. I don't think I can it faster without a more powerful computer. __________________________________________________ _________ Sub normal() Dim n As Long, rsu, csu, blk, nr, nc, i As Integer nr = 55: nc = 9 Cells(1, 2) = "nr=" & nr Cells(2, 2) = "nc=" & nc n = 1: rsu = 0: csu = 1: blk = 1 Do While IsEmpty(Cells(n, 1)) = False rsu = rsu + 1 If rsu > nr Then rsu = 1 csu = csu + 1 If csu > nc Then csu = 1 blk = blk + 1 End If End If Cells(rsu - nr + (nr + 1) * blk, csu + 2) = Cells(n, 1) n = n + 1 Loop 'Putting in the page numbers For i = 1 To blk Cells((i - 1) * (nr + 1) + 1, 3) = "Page " & i & " of " & blk Next i End Sub __________________________________________________ ____________	rugila (214)
201669	2003-12-19 20:14:00	Hi rugila, I run that, and it takes about 18 seconds before I get an error message that says "400" (and an OK button). I click OK, and all the numbers are set up correctly... I wonder if its because I used 65536 rather than the 65500 you used. Would it be because its looking for cell 65537? Mike.	Mike (15)
201670	2003-12-19 20:16:00	That must be it - I removed the entry in the last cell (65536) and it worked without an error. Still took about 18 seconds though (must be my PC) Mike.	Mike (15)
201671	2003-12-19 23:17:00	Hi Rugila, I am interested :-) Any possibility of you commenting the code? Its difficult to know what some variables represent etc without comments. Ive put some comments already but perhaps you can fill in the gaps for me. I tried this but for 65,500 cells it took 56 seconds on my PC (only an 866). I use a procedure to put in dummy data of FileName1, FileName2 etc. in column A so may be you can replicate this and see if it makes a difference (I doubt it will). Ive included this procedure in case you want it. cheers parry Sub normal() 'Transfer data in column A (source)to a matrix of 'rows x columns (target) per printed page. Dim n As Long, rsu, csu, blk, nr, nc, i As Integer 'nr = # rows per page in target range 'nc = # columns per page in target range nr = 55: nc = 9 'Why do this? Just to display to the user? Cells(1, 2) = " nr= " & nr Cells(2, 2) = " nc= " & nc 'what do variables n, rsu, csu and blk represent? 'n = row # in source range, rsu = ????, csu = ???? 'blk = ???? n = 1: rsu = 0: csu = 1: blk = 1 'loop through non-blank cells doing what? Hard to know unless 'you know what the variables represent. Do While IsEmpty(Cells(n, 1)) = False rsu = rsu + 1 If rsu > nr Then rsu = 1 csu = csu + 1 If csu > nc Then csu = 1 blk = blk + 1 End If End If 'Need to understand variables to know what this calc is doing 'Its obviously defining a cell reference in the target but 'what is the logic behind the calculation Cells(rsu - nr + (nr + 1) * blk, csu + 2) = Cells(n, 1) n = n + 1 Loop 'Putting in the page numbers For i = 1 To blk Cells((i - 1) * (nr + 1) + 1, 3) = " Page " & i & " of " & blk Next i End Sub Procedure to fill data in column A Sub FillData() Dim i As Long, FillArray() As String, n As Long n = 65500 [a:a].ClearContents ReDim FillArray(1 To n, 1 To 1) For i = 1 To n FillArray(i, 1) = " FileName " & i Next i Range(Cells(1, 1), Cells(n, 1)) = FillArray End Sub	parry (27)
201672	2003-12-20 08:48:00	Hi Rugila, beat this then - 4 secs!!! The best I could get on your one was 12 secs :-) I have put loads of comments so you can see if I have misinterpreted your previous code. Also how did you work out that bloody calculation for the target cell row? Could you explain it to us lesser mortals please. Your a pretty smart cookie. ;-) All that I did was put this into an array instead of from cell to cell, then at the end whacked the array in. Other than that I left your code largely unchanged. What do you get with this one Mike? Sub normal2() ' Transfer data in column A (source)to a matrix of ' rows x columns (target) per printed page. Dim n As Long, rsu As Long, csu As Integer, blk As Integer Dim nr As Long, nc As Integer, i As Integer, MyArray() As String Dim ArrNumRows As Long, NumPgs As Integer, x As Long ' Clear contents in target [c:q].ClearContents ' Obtain # rows and # columns per page nr = Cells(1, 2) ' nr = # rows per page in target range nc = Cells(2, 2) ' nc = # columns per page in target range ' initiate variables n = 1 ' n = row # in source range rsu = 0 ' rsu = row # in target range csu = 1 ' csu = column in target range blk = 1 ' blk = Total Page number ' Pre-calculations to determine array row dimensions ' Find # pages x = Application.WorksheetFunction.CountA([a:a]) If x Mod (nr * nc) = 0 Then NumPgs = x / (nr * nc) Else NumPgs = (x / (nr * nc)) + 1 End If ' Find total rows required NumRows = (nr + 1) * NumPgs ' Dimension target array ReDim MyArray(1 To NumRows, 1 To nc) ' 7448 ' loop through non-blank cells and place value into ' target cell. Leave a blank row between each group Do While IsEmpty(Cells(n, 1)) = False ' loop while source isnt empty rsu = rsu + 1 ' Add 1 to target row # ' Determine row/column numbers If rsu > nr Then ' If target row # is > # rows per page rsu = 1 ' reset target row # to 1 csu = csu + 1 ' increment target column # by 1 If csu > nc Then ' If target column # is > # columns per page csu = 1 ' reset target column # to 1 blk = blk + 1 ' increment total # pages End If End If ' Place value from source into target cell. Calcs as follows:- ' Row value of target cell is... ' TargetRow# - RowsPerPage + (RowsPerPage + 1) * CurrentPage# ' Column value of target cell is... ' TargetColumn# MyArray(rsu - nr + (nr + 1) * blk, csu) = Cells(n, 1) n = n + 1 ' increment the source row # Loop Range(Cells(1, 3), Cells(rsu - nr + (nr + 1) * blk, nc + 2)) = MyArray ' Putting in the page numbers. ' i = counter for page #, blk = total pages ' Calculation for target row is... ' (Page# - 1) * (#RowsPerPage + 1) + 1 For i = 1 To blk Cells((i - 1) * (nr + 1) + 1, 3) = " Page " & i & " of " & blk Next i End Sub	parry (27)
201673	2003-12-20 13:39:00	Hi Parry (and Mike and others if interested), Here's a commented version of what I think you asked for. I'm certainly interested in your 4 seconds but haven't had a chance to investigate that yet. I did reclassify 4,718,592 source data entries into blocks of 50 rows by 200 columns each - took 10 mins 17 seconds on my machine. (I did that by a minor modification of the code below, filling the first 72 columns with 65,536 entries in each and treating them as one long (4,718,592) source column. (As you see, I found it quite okay to use all 65536 cells in each column - didn't get any error messages at all.) Too slow, only about 7650 relocations per second, but as you suggest operating on arrays through ram is much faster than operating directly on the excel cells. I found that transferring filenames for instance took about twice as long as transferring numbers. Sub normal() 'Transfer data in column A (source)to a matrix of 'rows x columns (target) per printed page. dim a as date a=time Dim n As Long, rsu, csu, blk, nr, nc, i As Integer 'nr = # rows per page in target range 'nc = # columns per page in target range nr = 55: nc = 9 'Why do this? Just to display to the user? YES! Cells(1, 2) = " nr= " & nr Cells(2, 2) = " nc= " & nc 'what do variables n, rsu, csu and blk represent? 'Rsu=ROW SUBSCRIPT FOR TARGET BLOCK (MATRIX) - runs from 1 to 55 in 'each 'column in target block (unless source data ends first), then resets 'back to 1 for 'the next column. 'Csu=COLUMN SUBSCRIPT FOR TARGET BLOCK (MATRIX) - runs from 1 to 9 in 'each row in target block (unless source data ends first), then resets back 'to 1 for 'the next row. 'n = row # in source range, rsu = ????, csu = ???? 'blk = ???? 'blk = NUMBER OF EACH BLOCK (OR PAGE) IN THE TARGET RANGE n = 1: rsu = 0: csu = 1: blk = 1 'loop through non-blank cells doing what? Hard to know unless 'you know what the variables represent. 'THE LOOPING IS TO KNOW WHEN YOU HAVE RUN OUT OF SOURCE DATA TO 'RELOCATE INTO THE TARGET BLOCKS (PAGES). THE STOP WHEN ISEMPTY IS 'TRUE JUST ENSURES THE RUN ENDS WHEN THE SOURCE DATA ENDS, 'ALTERNATIVELY ENDS IF THERE IS A GAP IN THE SOURCE DATA. IT'S JUST A 'METHOD OF COUNTING THE NUMBER OF FILENAMES (OR WHATEVER) IN THE 'SOURCE DATA AS YOU GO ALONG (ON THE FLY?), RATHER THAN COUNTING 'ITALL AT THE START AND THEN DOING THE RELOCATION INTO THE TARGET 'BLOCKS. Do While IsEmpty(Cells(n, 1)) = False rsu = rsu + 1 If rsu > nr Then rsu = 1 'SET ROW SUBSCRIPT BACK TO 1 WHEN THE COLUMN csu HAS 'BEEN FILLED WITH nr entries csu = csu + 1 'ADVANCE COLUMN SUBSCRIPT FORWARD ONE WHEN THE 'COLUMN csu HAS BEEN FILLED If csu > nc Then csu = 1 'SET COLUMN SUBSCRIPT BACK TO 1 WHEN THE CURRENT 'BLOCK blk HAS BEEN FILLED WITH nc COLUMNS blk = blk + 1 'ADVANCE BLOCK NUMBER FORWARD 1 WHEN THE CURRENT 'BLOCK HAS BEEN FILLED WITH nr rows AND nc COLUMNS End If End If 'Need to understand variables to know what this calc is doing 'Its obviously defining a cell reference in the target but 'what is the logic behind the calculation Cells(rsu - nr + (nr + 1) * blk, csu + 2) = Cells(n, 1) 'THE csu+2 SHOULD I HOPE BE OK, THE +2 IS JUST TO MOVE (EACH COLUMN 'OF) THE TARGET BLOCKS 2 excel columns TO THE RIGHT FOR CONVENIENT 'LOCATION. THE rsu HERE GIVES THE NUMBER (SUBSCRIPT) OF EACH ROW 'IN THE TARGET OUTPUT. BUT THIS IS MOVED DOWN BY nr+1 FOR EACH 'NEW BLOCK, OTHERWISE EACH NEW BLOCK JUST REWRITES OVER THE 'PREVIOUS ONE IN THE SAME POSITION. THE +1 (in nr+1) IS TO LEAVE A 'GAP BETWEEN EACH BLOCK TO PUT IN THE PAGE NUMBERS. I COULD HAVE 'PUT IN THE CORRECT PAGE NUMBER IN EACH GAP ON THE FLY, BUT 'COULDN'T PUT IN THE TOTAL NUMBER OF BLOCKS UNTIL ALL THE COUNTING 'HAD FINISHED, i.e. when the ISEMPTY BECAME TRUE AND THE LOOP 'TERMINATED. 'THIS CALCULATION, AS YOU DESCRIBED IT, DID REQUIRE A BIT OF TRIAL 'AND ERROR ON MY PART TO GET THE TARGET LOCATIONS WHERE I WANTED 'THEM, BUT IT IS BASICALLY JUST A MEANS OF MAPPING OR TRANSFERRING 'THE SOURCE DATA INTO THE TARGET BLOCKS. THIS, AFTER ALL, IS WHAT 'THIS WHOLE EXERCISE WAS SUPPOSED TO ACHIEVE. n = n + 1 'PROCESS THE NEXT SOURCE DATA ENTRY IF ANY (i.e. if 'isempty=false) Loop 'Putting in the page numbers For i = 1 To blk Cells((i - 1) * (nr + 1) + 1, 3) = " Page " & i & " of " & blk Next i Range( " B1 " )=time - a 'get the computer to count how long (in minutes 'and seconds) the code takes to execute. Range( " B1 " ).numberformat= " mm:ss " End Sub	rugila (214)
201674	2003-12-20 16:50:00	Gentlemen No wish to hijack your thread, which is totally incomprehensible to me (and quite rightly, too - as I have no Excel & no mathematical ability whatsoever. I read this stuff in awe...) But Parry, you say your computer is "only an 866" - and that does have this newb intrigued. Please someone, what is an 866?	Laura (43)
201675	2003-12-20 20:24:00	Hi Laura, yes this thread is a bit on the geeky side. :-) In talking about an 866 I am meaning my PC has a Pentium III 866 processor. The number 866 refers to the CPU (Central Processing Unit) clock speed meaning how fast the PC is so the bigger the number the better. Now-a-days you have CPU's approaching 3000+ so mines a donkey by comparison. In regards to this thread if Rugila has a quicker PC than mine then he will be able to process the Excel code quicker than me. I hope this explains it for you. :-)	parry (27)
201676	2003-12-20 20:37:00	Hi Rugila, thanks muchly . I havent tried but I reckon you could do 4 . 7M under 4 minutes using an array . Also you should try turning off screen updating - I didnt notice any speed difference with the array version I used but for 4 . 7M it would probably make some difference . I'll give it a go later and see how fast I can get it to go . :-) I quite like the way you have done your code using one big Do/Loop . When I first looked at this I would have created nested For/Next loops for the #rows,#columns and #blocks but your is probably quicker since theres less loops . In my code above I worked out the # rows and # blocks I would need up front solely so I could dimension an array of the correct size . cheers . :D	parry (27)
201677	2003-12-20 22:01:00	> What do you get with this one Mike? As soon as I start the macro I get a message "Division by Zero". I wonder if there might be a difference in the versions coming into affect here? The code works fine for you two, but when I run it I get errors... I'm running Excel 2003. Mike.	Mike (15)
1 2 3 4