stacking data

R

redtwo

I have some data that I would like to stack. It has a few columns of
information and then nine statistics for a varying number of splits.
For example, for the first row it would be something like

A F G H I J K
name average average average stdev stdev stdev

where the average is listed for split1, split2, split3 and so on for
stdev. I would like to stack this so that it looks like

A B C D E
1 name average
2 name stdev

where C, D, and E now contain the average and stdev of the three
splits.

I am having a hard time doing this so that a) it does not take forever
(800 rows) and b) it is easy to code. For example, I tried using for
each row but it became difficult to deal with the inserting and
keeping track of where the for statement was. I am now in a loop but
still working with set range and I am trying to determine how to
delete a range of cells in a certain row. If I have a range named
rngdata and I want to work with one row say rngdata.row(1), how do I
access the 8-16th columns of this row? Here is a bit of code to help
with visualization. NumStat, NumSplit, NumParam, and NumCols are all
integers as are i and j.

Set RngData = Range(Range("A1").Offset(NumCat + 1, 0), _
Range("A1").Offset(NumCat + NumParam - 1, NumSplit * NumStat +
NumCols))
For i = 1 To NumParam * NumStat + NumStat
j = (i - 1) Mod NumStat
If j <> 0 Then
RngData.Rows(i - 1).Copy
RngData.Rows(i).Insert shift:=xlDown
With RngData
.Cells(i, NumCols + 1) = StrStats(j)
.Rows(i).Font.Bold = False
???.Delete shift:=xlShiftToLeft 'here add access to
columns
End With
Else
RngData.Cells(i, 8) = StrStats(j)
RngData.Rows(i).Font.Bold = True
End If
Next i

Would it be faster to do this as an array? If so, how would I do
that?

Thanks in advance
H
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top