I have data that is returned from an AS400 query that I can transfer into
Excel. The A column lists all products that are found in the return from my
query. A typical array would be like: A1 = Product 1 / A2 = Product 2 / A3
= Product 2 / A4 = Product 2 / A5 = Product 3 / A6 = Product 3 / A7 =
Product 4 / A8 = Product 5.

The reason for the differing number of occurances might be store related.
Product 1 is in a single store, therefore requiring only 1 row of data.
Product 2 is found in 3 stores and therefore I need all 3 rows of data. etc.

In the above example, I'd like to use and Excel function, argument, and/or
macro thas has the effect of inserting 2 blank lines between Row 1 and Row 2,
moving the Row 2 contents to Row 4. Then I'd like the functionality to spot
the next product name-change and insert 2 rows there, etc. all the way to the
bottom of the data.

I'd wind up with;

Product 1

Product 2
Product 2
Product 2

Product 3
Product 3

Product 4

My sincere thanks if you can show me how to do this.

Hey Confused/Houston !! Former CorpusChristi Guy here !!
Paste the below code in a standard module


Sub InsertRow_A_Chg()
Dim Lrow As Long, vcurrent As String, i As Long
'// find last used cell in Column A
Lrow = Cells(Rows.Count, "A").End(xlUp).Row
'// get the value of that cell in Column A (column 1)
vcurrent = Cells(Lrow, 1).Value
'// rows are inserted by looping from bottom up
For i = Lrow To 2 Step -1 '<<< goes up to row 2 - change if needed
If Cells(i, 1).Value < vcurrent Then
vcurrent = Cells(i, 1).Value
Rows(i + 1).Resize(2).Insert '<< the 2 represent number of rows to
insert
End If
Next i
End Sub

Hope this helps...
Jim May


"ConfusedNHouston" wrote:

I have data that is returned from an AS400 query that I can transfer into
Excel. The A column lists all products that are found in the return from my
query. A typical array would be like: A1 = Product 1 / A2 = Product 2 / A3
= Product 2 / A4 = Product 2 / A5 = Product 3 / A6 = Product 3 / A7 =
Product 4 / A8 = Product 5.

The reason for the differing number of occurances might be store related.
Product 1 is in a single store, therefore requiring only 1 row of data.
Product 2 is found in 3 stores and therefore I need all 3 rows of data. etc.

In the above example, I'd like to use and Excel function, argument, and/or
macro thas has the effect of inserting 2 blank lines between Row 1 and Row 2,
moving the Row 2 contents to Row 4. Then I'd like the functionality to spot
the next product name-change and insert 2 rows there, etc. all the way to the
bottom of the data.

I'd wind up with;

Product 1

Product 2
Product 2
Product 2

Product 3
Product 3

Product 4

My sincere thanks if you can show me how to do this.