Add empty rows and move data

[FredrikNilsen]

Once a week or so I get an Excel file with data exported from SuperOffice. I need to move some of the data. The number of rows will vary, but the number of columns is always the same. I would like take the data from each cell in column E, and move it to a new empty row below. See illustration. I have found code to add rows, but it would be great if I could do it all in one operation.

 

[Zot]

Try this

Sub MoveData()

Application.ScreenUpdating = False
For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
    Range("A" & i).Offset(1).EntireRow.Insert
    Range("E" & i).Cut Range("D" & i).Offset(1)
Next
Application.ScreenUpdating = True

End Sub

 

[FredrikNilsen]

Try this

Sub MoveData()

Application.ScreenUpdating = False
For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
    Range("A" & i).Offset(1).EntireRow.Insert
    Range("E" & i).Cut Range("D" & i).Offset(1)
Next
Application.ScreenUpdating = True

End Sub

Perfect, thank you!

 

[FredrikNilsen]

Try this

Sub MoveData()

Application.ScreenUpdating = False
For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
    Range("A" & i).Offset(1).EntireRow.Insert
    Range("E" & i).Cut Range("D" & i).Offset(1)
Next
Application.ScreenUpdating = True

End Sub

Is it easy to change this to add the data from column E into the existing data in the corresponding cell in column D, preferably with a linebreak (alt+enter) before?

 

[Zot]

Is it easy to change this to add the data from column E into the existing data in the corresponding cell in column D, preferably with a linebreak (alt+enter) before?

You meant like this?

Sub MoveData()

Application.ScreenUpdating = False
For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
    Range("A" & i, "D" & i).VerticalAlignment = xlVAlignTop
    Range("D" & i) = Range("D" & i) & vbLf & Range("E" & i).Value
    Range("E" & i).ClearContents
Next
Application.ScreenUpdating = True

End Sub

 

[FredrikNilsen]

You meant like this?

Sub MoveData()

Application.ScreenUpdating = False
For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
    Range("A" & i, "D" & i).VerticalAlignment = xlVAlignTop
    Range("D" & i) = Range("D" & i) & vbLf & Range("E" & i).Value
    Range("E" & i).ClearContents
Next
Application.ScreenUpdating = True

End Sub

Amazing! I have to read up on this stuff. Thank you!

 

[Zot]

Amazing! I have to read up on this stuff. Thank you!

This is just simple step
1) The loop starts from end of row to top so that it will not affect the i row increment
2) Range("A" & i, "D" & i).VerticalAlignment = xlVAlignTop is to align characters to the top (look nicer) since the default is to the bottom if adding Alt+Enter
3) Range("D" & i) = Range("D" & i) & vbLf & Range("E" & i).Value rewrite data in Range("D" & i) with combination of range D and E. The vbLf (VB Linefeed) = Alt+Enter.
4) Range("E" & i).ClearContents is self-explanatory.