So... i have a sheet with a lot of lines and for each line I have between 0 and 5 information's (one in its own column) i need to spread out so only 1 column.
Eksample:
<tbody>
</tbody>
This i need to get to this form:
<tbody>
</tbody>
I think im ½ the way
First i made a Macro that filled in extra lines based on a count of how may cells in a row had X ( this i do in column P) - So for order S001 I auto generated 1 new line and took all data in other columns and pasted it down:
My problem now is ... how to i make it take part 1 ... skip the blank Part 2.. and then take part 3 ?
Eksample:
Order | part1 | part2 | par3 | Numbers |
S001 | X | x | 24 | |
S009 | x | 10 | ||
S011 | x | x | x | 15 |
S115 | 10 |
<tbody>
</tbody>
This i need to get to this form:
Order | part | number |
S001 | part1 | 24 |
S001 | Part3 | 24 |
S009 | Part2 | 10 |
S011 | part1 | 15 |
S011 | Part2 | 15 |
S011 | Part3 | 15 |
<tbody>
</tbody>
I think im ½ the way
First i made a Macro that filled in extra lines based on a count of how may cells in a row had X ( this i do in column P) - So for order S001 I auto generated 1 new line and took all data in other columns and pasted it down:
Code:
Sub Insert() Dim End_Row As Long, n As Long, Ins As Long
End_Row = Range("P" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For n = End_Row To 1 Step -1
Ins = Cells(n, "P").Value
If Ins > 1 Then
Range("P" & n + 1 & ":P" & n + Ins - 1).EntireRow.Insert
Range(Cells(n, 1), Cells(n + Ins - 1, "P")).FillDown
Range(Cells(n, 1), Cells(n + Ins - 1, "Q")).FillDown
Range(Cells(n, 1), Cells(n + Ins - 1, "R")).FillDown
Range(Cells(n, 1), Cells(n + Ins - 1, "S")).FillDown
Range(Cells(n, 1), Cells(n + Ins - 1, "T")).FillDown
Range(Cells(n, 1), Cells(n + Ins - 1, "U")).FillDown
Range(Cells(n, 1), Cells(n + Ins - 1, "V")).FillDown
' Det her virker desvære ikke
' ElseIf Ins < 0 Then
' Range("P" & n + 1 & ":P" & n + Ins - 1).EntireRow.Delete
End If
Next n
Application.ScreenUpdating = True
End Sub
My problem now is ... how to i make it take part 1 ... skip the blank Part 2.. and then take part 3 ?
Last edited: