Move part of row to 2nd row, another part of row to 3rd row

**buggseyosu** · 05-02-2014, 11:43 PM

I have an issue and I don't know if there's a way out. I have a large spreadsheet (8000 Rows) that I need to manipulate, where data was put into additional columns when it should've been put into a 2nd and 3rd row instead. Each entry spans one large row instead of 3 small rows as it should be. So now I need to create two new rows after each existing row. Then take data in columns C-F, and move it to the next row (columns A-D). Then take columns G-H and move that to the 3rd row in the series (Columns A-B).

So an example would be:

Original Data:

A1 A2 A3 A4 A5 A6 A7 A8

B1 B2 B3 B4 B5 B6 B7 B8

Resulting Data:

A1 A2
A3 A4 A5 A6
A7 A8

B1 B2
B3 B4 B5 B6
B7 B8

And so on, until 8000 rows becomes 24000 (or 32000) rows. Is there any help for me out there?

Thanks!

**jmac1947** · 05-03-2014, 02:48 AM

Hi, and welcome to the forums.

Please take the time to read the forum rules to gain an understanding of "good form". It is always a great idea to post a sample workbook even for seemingly simple requests and you often get a working solution back as a bonus.

Your problem is reasonably simple. I am guessing that as this is your first post that you do not have much VBA experience. I came across a couple of very useful sites for new people in VBA which i suggest you have a look at:

http://www.homeandlearn.org/index.html
http://excelvbatutor.com/vba_tutorial.html

Post a sample workbook to use and I will take a look for you. Add an attachment by using the "Go Advanced" link below this reply input panel

Cheers

Jmac

**HaHoBe** · 05-03-2014, 02:58 AM

Hi, buggseyosu,

assuming that the data is in a continuing range of rows by now and handles values (no formulas) starting with A1 you could try the following code on a copy of the worksheet:

Please Login or Register  to view this content.

How to install your new code

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

Ciao,
Holger

**buggseyosu** · 05-04-2014, 06:58 PM

Hi Jmac,

Thank you for the reply. I am new to the forum so thank you for the info, and I will read those links. As for a sample, I have created one with two tabs: a current tab and future state tab. I added headers but that's just for readability, I will not have headers in my actual data.

Thanks again!