Solved

Excel 2010 - split one row into 2 rows and offset

Posted on 2013-05-22
3
704 Views
Last Modified: 2013-05-23
I would like either vba code or a macro to split the attached spreadsheet as follows:

A1:V1 - Remain as titles
A2:V2 - Remain in row 2
Insert new row and move W2:AK2 to this row as follows
W2 would become F3, X2 would become G3 and so on

Carry on for the remaining 244 rows
Book1.xlsx
0
Comment
Question by:andymacf
  • 2
3 Comments
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 39188289
Hi, andymacf.

Please see attached. Couple of points...
(1) I changed the contents of columns A and W for testing purposes.
(2) It's not the fastest code, but it's OK for a few hundred rows. Let me know if you are planning to run this against tens of thousands.

The code is...
Option Explicit

Sub Split_Rows()
Dim i As Long
Dim xLast_Row As Long

ThisWorkbook.Sheets("Sheet1").Activate

xLast_Row = ActiveSheet.UsedRange.Cells(1, 1).Row + ActiveSheet.UsedRange.Rows.Count - 1
If xLast_Row < 2 Then
    MsgBox ("No data found - run cancelled.")
    Exit Sub
End If

Application.ScreenUpdating = False
    
    For i = xLast_Row To 2 Step -1
    
        Rows(i + 1).Insert Shift:=xlShiftDown
    
        Range("W" & i & ":AK" & i).Cut Destination:=Range("F" & i + 1 & ":T" & i + 1)
    
    Next
    
Columns("W:AK").Delete Shift:=xlToLeft

Application.ScreenUpdating = True

MsgBox "Run complete - " & xLast_Row - 1 & " rows processed."

End Sub

Open in new window

Thanks,
Brian.Book1b-V2.xlsm
0
 
LVL 7

Author Closing Comment

by:andymacf
ID: 39190140
Fantastic job, this file will only ever be run against the 245 rows so it is a perfect fit.
Thanks Brian, much appreciated.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 39190485
Thanks, andymacf, glad to help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Macro Capitalize 4 53
Hiding column macro 10 28
How to add the word OR to the front of each Excel value 4 36
Boolean help 6 27
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now