Remember that when you write: My. Array = Range("A1: A5. My. Array = Range("A1: A5. Value. You can also use names: My. Array = Names("My. WSTable"). Refers. To. Range. Value. But Value is not the only property of Range. I have used: My. Array = Range("A1: A5. Number. Format. I doubt. My. Array = Range("A1: A5. Font. would work but I would expect. My. Array = Range("A1: A5. Font. Bold. to work. ![]() I do not know what formats you want to copy so you will have to try. However, I must add that when you copy and paste a large range, it is not as much slower than doing it via an array as we all thought. Post Edit information. Having posted the above I tried by own advice. My experiments with copying Font. ![]() Color and Font. Bold to an array have failed. Of the following statements, the second would fail with a type mismatch: Value. This is the website of!K7, the!K7 label group is home to some of the most diverse and internationally acclaimed artists worldwide. It is currently running four. Tabtight professional, free when you need it, VPN service. Un libro รจ un insieme di fogli, stampati oppure manoscritti, delle stesse dimensioni, rilegati insieme in un certo ordine e racchiusi da una copertina. Array = . Range("A1: T5. Value. Colour. Array = . Range("A1: T5. 00. Font. Color. Value. Array must be of type variant. I tried both variant and long for Colour. Array without success. I filled Colour. Array with values and tried the following statement: . Range("A1: T5. 00. Font. Color = Colour. Array. The entire range would be coloured according to the first element of Colour. Array and then Excel looped consuming about 4. I terminated it with the Task Manager. There is a time penalty associated with switching between worksheets but recent questions about macro duration have caused everyone to review our belief that working via arrays was substantially quicker. I constructed an experiment that broadly reflects your requirement. I filled worksheet Time. With version 1, I copied every 7th cells from worksheet "Time. Time. 2" using copy. With version 2, I copied every 7th cells from worksheet "Time. Time. 2" by copying the value and the colour via an array. With version 3, I copied every 7th cells from worksheet "Time. Time. 2" by copying the formula and the colour via an array. Version 1 took an average of 1. Version 1 copied formulae and all formatting, version 2 copied values and colour while version 3 copied formulae and colour. With versions 1 and 2 you could add bold and italic, say, and still have some time in hand. However, I am not sure it would be worth the bother given that copying 2. Code for Version 1**I do not think this code includes anything that needs an explanation. Respond with a comment if I am wrong and I will fix. Sub Selection. Copy. And. Paste(). Dim Col. Dest. Crnt As Integer. Dim Col. Src. Crnt As Integer. Dim Num. Select As Long. Dim Row. Dest. Crnt As Integer. Dim Row. Src. Crnt As Integer. Dim Start. Time As Single. Application. Screen. Updating = False. Application. Calculation = xl. Calculation. Manual. Num. Select = 1. Col. Dest. Crnt = 1. Row. Dest. Crnt = 1. With Sheets("Time. Range("A1: T7. 15"). Entire. Row. Delete. Start. Time = Timer. Do While True. Col. Src. Crnt = (Num. Select Mod 2. 0) + 1. Row. Src. Crnt = (Num. Select - Col. Src. Crnt) / 2. 0 + 1. If Row. Src. Crnt > 5. Then. Sheets("Time. Cells(Row. Src. Crnt, Col. Src. Crnt). Copy _. Destination: =Sheets("Time. Cells(Row. Dest. Crnt, Col. Dest. Crnt). If Col. Dest. Crnt = 2. 0 Then. Col. Dest. Crnt = 1. Row. Dest. Crnt = Row. Dest. Crnt + 1. Col. Dest. Crnt = Col. Dest. Crnt + 1. Num. Select = Num. Select + 7. Debug. Print Timer - Start. Time. ' Average 1. Application. Calculation = xl. Calculation. Automatic. Code for Versions 2 and 3**The User type definition must be placed before any subroutine in the module. The code works through the source worksheet copying values or formulae and colours to the next element of the array. Once selection has been completed, it copies the collected information to the destination worksheet. This avoids switching between worksheets more than is essential. Type Value. Dtl. Value As String. Colour As Long. Sub Selection. Via. Array(). Dim Col. Dest. Crnt As Integer. Dim Col. Src. Crnt As Integer. Dim Inx. VLCrnt As Integer. Dim Inx. VLCrnt. Max As Integer. Dim Num. Select As Long. Dim Row. Dest. Crnt As Integer. Dim Row. Src. Crnt As Integer. Dim Start. Time As Single. Dim Value. List() As Value. Dtl. Application. Screen. Updating = False. Application. Calculation = xl. Calculation. Manual. I have sized the array to more than I expect to require because Re. Dim. ' Preserve is expensive. However, I will resize if I fill the array. For my experiment I know exactly how many elements I need but that. Re. Dim Value. List(1 To 2. Num. Select = 1. Col. Dest. Crnt = 1. Row. Dest. Crnt = 1. Inx. VLCrnt. Max = 0 ' Last used element in Value. List. With Sheets("Time. Range("A1: T7. 15"). Entire. Row. Delete. Start. Time = Timer. With Sheets("Time. Do While True. Col. Src. Crnt = (Num. Select Mod 2. 0) + 1. Row. Src. Crnt = (Num. Select - Col. Src. Crnt) / 2. 0 + 1. If Row. Src. Crnt > 5. Then. Exit Do. Inx. VLCrnt. Max = Inx. VLCrnt. Max + 1. If Inx. VLCrnt. Max > UBound(Value. List) Then. ' Resize array if it has been filled. Re. Dim Preserve Value. List(1 To UBound(Value. List) + 1. 00. 0). With . Cells(Row. Src. Crnt, Col. Src. Crnt). Value. List(Inx. VLCrnt. Max). Value = . Value ' Version 2. Value. List(Inx. VLCrnt. Max). Value = . Formula ' Version 3. Value. List(Inx. VLCrnt. Max). Colour = . Font. Color. Num. Select = Num. Select + 7. With Sheets("Time. For Inx. VLCrnt = 1 To Inx. VLCrnt. Max. With . Cells(Row. Dest. Crnt, Col. Dest. Crnt). . Value = Value. List(Inx. VLCrnt). Value ' Version 2. Formula = Value. List(Inx. VLCrnt). Value ' Version 3. Font. Color = Value. List(Inx. VLCrnt). Colour. If Col. Dest. Crnt = 2. 0 Then. Col. Dest. Crnt = 1. Row. Dest. Crnt = Row. Dest. Crnt + 1. Col. Dest. Crnt = Col. Dest. Crnt + 1. Debug. Print Timer - Start. Time. ' Version 2 average 1. Version 3 average 1. Application. Calculation = xl. Calculation. Automatic.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
October 2017
Categories |