Move Part of a Row Up and Over

meuby

New Member
Joined
Mar 14, 2019
Messages
3
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel
How about
Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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...
Code:
[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

New Member
Joined
Mar 14, 2019
Messages
3
Hi & welcome to MrExcel
How about
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're welcome & thanks for the feedback
 

meuby

New Member
Joined
Mar 14, 2019
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,625
Messages
5,523,970
Members
409,548
Latest member
Buildingbysea

This Week's Hot Topics

Top