Hello,
I have a worksheet with data presented in repeating clusters which I would
like to rearrange into a single row. There are 5 items spread across 2
colums and 5 rows. Would it make sense to assign the cell values to an
array, then print that array to a single row and repeat for as many clusters?
Code is below:
Thanks in advance!

Sub FormatRatings()
Dim RateData As Variant
Dim AsOf As Date
Dim a As Long, b As Long, c As Long, d As Long, e As Long, x As Long
'a through e are cell row references
'x = Load Sheet row
'Starting reference
a = 12
b = 13
c = 14
d = 15
c = 16
x = 2

For i = 1 To 30 Step 1
'Gather Data
Sheets(2).Select
RateData = Array(AsOf, Cells(a, 1).Value, Cells(b, 2).Value, Cells(c,
2).Value, Cells(d, 2).Value, Cells(e, 2).Value)
'Print Data
Sheets(1).Select
Range(Cells(x, 1), Cells(x, 6)).Value = RateData

'Increment reference locations
a = a + 5
b = b + 5
c = c + 5
d = d + 5
e = e + 5
x = x + 1

Next i
End Sub

Anything which reduces I/O helps.

But if the whole procedure takes seconds then trying to optimize may not be
worth the effort...

One can also argue that if speed is critical then VBA is not a good
choice... :-)

Declare the Array in the beginning and not create one like you do with each
iteration.
Assign each element in a loop...


"Bythsx-Addagio" wrote:

Hello,
I have a worksheet with data presented in repeating clusters which I would
like to rearrange into a single row. There are 5 items spread across 2
colums and 5 rows. Would it make sense to assign the cell values to an
array, then print that array to a single row and repeat for as many clusters?
Code is below:
Thanks in advance!

Sub FormatRatings()
Dim RateData As Variant
Dim AsOf As Date
Dim a As Long, b As Long, c As Long, d As Long, e As Long, x As Long
'a through e are cell row references
'x = Load Sheet row
'Starting reference
a = 12
b = 13
c = 14
d = 15
c = 16
x = 2

For i = 1 To 30 Step 1
'Gather Data
Sheets(2).Select
RateData = Array(AsOf, Cells(a, 1).Value, Cells(b, 2).Value, Cells(c,
2).Value, Cells(d, 2).Value, Cells(e, 2).Value)
'Print Data
Sheets(1).Select
Range(Cells(x, 1), Cells(x, 6)).Value = RateData

'Increment reference locations
a = a + 5
b = b + 5
c = c + 5
d = d + 5
e = e + 5
x = x + 1

Next i
End Sub