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
>