Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an Array to Speed-Up Range Copy
I have a large row of formulas (183 cells) in a spreadsheet that I
want to copy a variable number of times (could be as many as 2,000). My current working code: With Sheets("WORKPLACE") .Range(.Cells(5, 4), .Cells(5, 4).Offset(0, 182)).Copy _ Destination:=.Range(.Cells(6, 4), .Cells(totalrows, 4)) End With My thought is to create a variant array: vtFormulas = WorksheetFunction.Transpose(WorksheetFunction.Tran spose(ActiveSheet.Range _ (Cells(5, 4), Cells(5, 186)))) What is the best way to copy this array down from Cells(6, 4) to Cells(totalrows, 4)? I want cell references to change relative to the respective row. Thanks for a response. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an Array to Speed-Up Range Copy
With Worksheets("WORKPLACE")
.Cells(5, 4).Resize(totalrows, 183).FillDown 'or .Range("D5").Resize(totalrows, 183).FillDown End With -- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an Array to Speed-Up Range Copy
Thanks very much, Bill.
I gave up on the variant array and used your code; it appears to be extremely efficient. JingleRock |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an Array to Speed-Up Range Copy
I tried to apply your code to copying a single column, with variable
length, to a cell on another worksheet; it did not work. Is this possible? JingleRock |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an Array to Speed-Up Range Copy
In general, to copy a whole sheet of data from one sheet to another, use
code like the following. Change the names of the worksheets to whatever you need. Public Sub Test() Dim wsSource As Worksheet Dim wsDest As Worksheet Dim rngSource As Range Set wsSource = Worksheets("Source") Set wsDest = Worksheets("Dest") Set rngSource = wsSource.UsedRange rngSource.Copy wsDest.Range("A1") End Sub -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy a range into an array | Excel Programming | |||
What is the fastest way to copy a range to a 2D array? | Excel Programming | |||
copy one array formula to an array range | Excel Programming | |||
Copy Range and Paste to Array of Sheets | Excel Programming | |||
How Can I copy all value of the array into the range? | Excel Programming |