Improve company productivity with a Business Account.Sign Up

x
?
Solved

Excel 2010 - split one row into 2 rows and offset

Posted on 2013-05-22
3
Medium Priority
?
745 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 2000 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Excel allows various different methods to link Excel files to each other. This includes relative paths, mapped drives (or the local drive) and UNC paths. UNC paths are the least robust of the three.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

607 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