Can anyone help me understand how to populate a temporary
array by row please. I am trying to take a column of
values H1:H256 on one sheet (Values change every time
sheet is calculated), and populate another sheet in the
range A2:IV1001. The current macro runs 1000 times and
works but is very slow, (even without selecting
anything). I thought that it might be quicker to
populate a temporary array row by row and then paste the
temporary array into the worksheet. I can understand the
logic in the following, but it does it cell by cell, and
what I'm struggling to understand is how I adapt it to
populate it row by row


Sub TranData2()

Dim i As Long
Dim TempArray() As Single
Dim TheRange As Range

ReDim TempArray(3 To 10, 4 To 16)
Set TheRange = Range(Cells(3, 4), Cells(10, 16))

CurrVal = 0
x = 1
For i = 3 To 10
For j = 4 To 16
TempArray(i, j) = Range("A3").Value
ActiveSheet.Calculate
Next j
Application.StatusBar = x
x = x + 1
Next i

TheRange.Value = TempArray

End Sub


I assume that I should use worksheetfunction.transpose to
transpose my column of values and then put them straight
into the temporary array, repeating and moving down a row
each time, but I can't work out how to do this.

The following (Ranges are test ranges only) is not
working but will give you some idea of what I'm trying to
achieve. Am I even on the right lines here?

Sub TranData()

Dim i As Long
Dim TempArray() As Long
Dim TheRange As Range

ReDim TempArray(3 To 10, 4 To 16)
Set TheRange = Range(Cells(3, 4), Cells(10, 16))

x = 1
For i = 3 To 10
TempArray(i) = WorksheetFunction.Transpose
("ColData").Value
ActiveSheet.Calculate
Application.StatusBar = x
x = x + 1
Next i

TheRange.Value = TempArray

End Sub

Thanks
Pete

I tried just writing a macro that did what I think you want rather than to
try to use one of your techniques. I don't know how fast you need it to be
but this takes 2 or 3 seconds on my 2.66Ghz machine. Of course a lot
depends on how long a calc takes.

Sub a()
Dim Counter As Integer
Application.ScreenUpdating = False
For Counter = 1 To 1000
With Worksheets("SrcSheet")
.Calculate
.Range("H1:H256").Copy
End With
Worksheets("DestSheet").Cells(Counter + 1, 1).PasteSpecial
xlPasteValues, , , True
Next
End Sub


--
Jim Rech
Excel MVP

Thanks for replying. The routine I've got now is pretty
much the same as that, but seems to take an age. Copy
range, paste transpose, increment row, repeat (No
selecting involved and screen updating turned off).
Machines are 700Mhz pentiums with 256MB RAM, Workbook is
fairly large and as the range fills up it slows down a
lot. Was hoping a Temporary Array would be quicker - Was
also curious how to actually populate one by row as
opposed to by element. Hadn't thought to check how long
it takes to calc though, so will half populate it and
then look at that as well.

Thanks.
Pete

-----Original Message-----
I tried just writing a macro that did what I think you
want rather than to
try to use one of your techniques. I don't know how
fast you need it to be
but this takes 2 or 3 seconds on my 2.66Ghz machine. Of
course a lot
depends on how long a calc takes.

Sub a()
Dim Counter As Integer
Application.ScreenUpdating = False
For Counter = 1 To 1000
With Worksheets("SrcSheet")
.Calculate
.Range("H1:H256").Copy
End With
Worksheets("DestSheet").Cells(Counter + 1,
1).PasteSpecial
xlPasteValues, , , True
Next
End Sub


--
Jim Rech
Excel MVP


.

Call me lazy but trying to understand your code is giving
me a headache (iterating two dimensional arrays.. ugh),
but from your description, I assume you are recalculating
your first sheet after transposing 1 row.

i.e. the loop does:

1) Read first range (h1:h256)
2) Transpose to (A2:IV2)
3) Recalculate

-- next iteration

If this is what you are doing, then you can try the
following code and you should see a significant
performance gain over the single cell way you say you are
doing it.

It annoyingly flickers during this as it constantly
switches worksheets. Don't know how to avoid that.

---begin code-------------------------------------------

Sub TranData()

Dim rng1 As Range
Dim rng2 As Range

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Dim myRange As String
Dim wRow As Integer 'Write Row

Set ws1 = ActiveSheet
Set ws2 = ActiveWorkbook.Worksheets("Sheet2")

Set rng1 = ws1.Range("h1:h256")


For wRow = 2 To 1001
myRange = "A" & wRow & ":IV" & wRow
Set rng2 = ws2.Range(myRange)

rng1.Copy '1)
rng2.PasteSpecial xlPasteValues, _
xlPasteSpecialOperationNone, , True '2
ws1.Calculate '3
Next wRow

End Sub

---end code-------------------------------------------

Sorry, my fault for not being clear. What you have given
me is pretty much what I am using (My code is almost
identical to Jim's post), but it runs slowly. I had
thought that perhaps not writing anything to the
worksheet until the end would be quicker and hence I was
playing with temp arrays. The first single cell example
was one from a book by John Walkenbach and I was just
trying to see if I could do something similar row by row
as opposed to cell by cell. I am not using this cell by
cell method in my workbook.

Thanks


-----Original Message-----
Call me lazy but trying to understand your code is
giving
me a headache (iterating two dimensional arrays.. ugh),
but from your description, I assume you are
recalculating
your first sheet after transposing 1 row.

i.e. the loop does:

1) Read first range (h1:h256)
2) Transpose to (A2:IV2)
3) Recalculate

-- next iteration

If this is what you are doing, then you can try the
following code and you should see a significant
performance gain over the single cell way you say you
are
doing it.

It annoyingly flickers during this as it constantly
switches worksheets. Don't know how to avoid that.

---begin code-------------------------------------------

Sub TranData()

Dim rng1 As Range
Dim rng2 As Range

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Dim myRange As String
Dim wRow As Integer 'Write Row

Set ws1 = ActiveSheet
Set ws2 = ActiveWorkbook.Worksheets("Sheet2")

Set rng1 = ws1.Range("h1:h256")


For wRow = 2 To 1001
myRange = "A" & wRow & ":IV" & wRow
Set rng2 = ws2.Range(myRange)

rng1.Copy '1)
rng2.PasteSpecial xlPasteValues, _
xlPasteSpecialOperationNone, ,
True '2
ws1.Calculate '3
Next wRow

End Sub

---end code-------------------------------------------

.

Fixed!! It was your comment about how long a calc takes
that got me there. There were other large worksheets with
lots of calculations, arrays, charts etc that didn't need
to do anything until all the samples were read in that
were recalcing each time and slowing it down horribly.
Turned EnableCalculation status to False for all of them
until the end and a phenomenal difference in speed -
Thanks Jim.
Pete


-----Original Message-----
I tried just writing a macro that did what I think you
want rather than to
try to use one of your techniques. I don't know how
fast you need it to be
but this takes 2 or 3 seconds on my 2.66Ghz machine. Of
course a lot
depends on how long a calc takes.

Sub a()
Dim Counter As Integer
Application.ScreenUpdating = False
For Counter = 1 To 1000
With Worksheets("SrcSheet")
.Calculate
.Range("H1:H256").Copy
End With
Worksheets("DestSheet").Cells(Counter + 1,
1).PasteSpecial
xlPasteValues, , , True
Next
End Sub


--
Jim Rech
Excel MVP


.

Turned EnableCalculation status to False

Great idea, Pete. I'm glad you worked it out.

--
Jim Rech
Excel MVP