Insert rows based on number in cell

[manmah]

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.

Thanks anyone in advance.

Mark

 

[Jorge_B]

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:

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.

 

[manmah]

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

 

[Jorge_B]

You're welcome. Thank you for the feedback.