# Insert rows based on number in cell

#### manmah

##### Board Regular
Hi There

Is there any code I can use that will insert rows dependant on a number in a cell?

I have a report which I have put into excel. There is a reference in column H, then in column I the number of containers relating to that ref. In column J there are the ref numbers of those containers separated by a comma. I have used text to columns to separate these into multiple columns (some are up to 13, some are only 1 or 2) but now want to transpose those references in columns J onwards to list down in column J and relate them to the ref in col H.

Bit confusing I know but its sounds right in my head just cannot work out how to do it.

Mark

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi,
It is a little bit confusing, but I think you want to transpose column I, J, K and so on to the end, putting them as rows below the reference.
For instance if you have this:

H I J K L
Reference1 3 Container1 Container2 Container3
Reference2 2 Container1 Container2

Convert to this:

H I
Reference1 3
Container1
Container2
Container3
Reference2 2
Container1
Container2

If that is what you're looking for, you could try this:

Code:
``````Sub TransRefs()
Dim NContainers     As Long
Dim CurrCell        As Range
Dim TransposeRng    As Range
Dim ContainersRefs  As Range
Set CurrCell = Range("H2")
Do Until CurrCell = vbNullString
NContainers = CurrCell.Offset(0, 1).Value
Set ContainersRefs = Range(CurrCell.Offset(0, 2), CurrCell.Offset(0, 2).End(xlToRight))
For i = 1 To NContainers
CurrCell.Offset(1, 0).EntireRow.Insert
Next i
Set TransposeRng = CurrCell.Offset(1, 0).Resize(CurrCell.Rows.Count + NContainers - 1)
TransposeRng.FormulaArray = Application.WorksheetFunction.Transpose(ContainersRefs)
Set CurrCell = CurrCell.Offset(NContainers + 1, 0)
Loop
End Sub``````

Please note that it assumes references start H2.
Hope it helps.

Thank you that is brilliant. I can utilise that in many things. Thanks

You're welcome. Thank you for the feedback.

Replies
3
Views
104
Replies
5
Views
454
Replies
0
Views
132
Replies
4
Views
71
Replies
10
Views
399

1,203,051
Messages
6,053,220
Members
444,648
Latest member
sinkuan85

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back