Move Part of a Row Up and Over

[meuby]

Wondering if there's a way with VBA to do the following.


I need to get the data in C:2 and D:2 into E:1 and F:1, the data from C:3 and D:3 into G:1 and H:1. I need to repeat for the entire sheet. Can this be done with VBA?

https://drive.google.com/open?id=189R34HQhkflk6wh-mlxhj6aJtblTX5Ed

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub meuby()
   Dim rng As Range
   
   For Each rng In Range("B:B").SpecialCells(xlBlanks).Areas
      rng.Offset(, 1).Resize(1, 2).Copy rng.Offset(-1, 3).Resize(1, 2)
      rng.Offset(1, 1).Resize(1, 2).Copy rng.Offset(-1, 5).Resize(1, 2)
   Next rng
End Sub

 

[Rick Rothstein]

Wondering if there's a way with VBA to do the following.


I need to get the data in C:2 and D:2 into E:1 and F:1, the data from C:3 and D:3 into G:1 and H:1. I need to repeat for the entire sheet. Can this be done with VBA?

See if this macro does what you want...

[table="width: 500"]
[tr]
	[td]Sub UpAndOver()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Range("E1").Resize(, 2 * LastRow - 2) = Split(Join(Application.Transpose(Evaluate(Replace("C2:C#&char(9)&D2:D#", "#", LastRow))), Chr(9)), Chr(9))
End Sub[/td]
[/tr]
[/table]

 

[meuby]

Hi & welcome to MrExcel
How about

Sub meuby()
   Dim rng As Range
   
   For Each rng In Range("B:B").SpecialCells(xlBlanks).Areas
      rng.Offset(, 1).Resize(1, 2).Copy rng.Offset(-1, 3).Resize(1, 2)
      rng.Offset(1, 1).Resize(1, 2).Copy rng.Offset(-1, 5).Resize(1, 2)
   Next rng
End Sub

This worked - THANK YOU!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Rick Rothstein]

This worked - THANK YOU!!

Just wondering... did the code I posted work as well or did it not work for you?

 

[meuby]

Just wondering... did the code I posted work as well or did it not work for you?

Hi - I apologize for the delay.

Actually, the macro you provided moved everything up and across, but it moved it all to row 1.

Thank you...hope this feedback helps you.