Solved

VBA Excel script to add columns and convert to single row per item.

Posted on 2014-03-03
2
452 Views
Last Modified: 2014-03-04
Hello Experts

I'm looking for a script that will convert a spreadsheet from 2 rows per item to 1 row per item.

In order to preserve the flow of the data, I would like to insert a new column after each column from C to Y, rather than moving all the data in the 2nd row as a single block.

Contents of fields are mix of data and formulas.  Number of rows is 5000.

Thanks!
ee-script.xls
0
Comment
Question by:tomfolinsbee
2 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 39903810
With the requirement of keeping the formula, it makes it hard to do batch processing. You can try the attached macro, but I expect it to be slow.

Sub Macro6()
Dim lLastRow As Long, lRowLoop As Long, lColLoop As Long

'turn off updates to speed up code execution
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
End With

lLastRow = Cells(Rows.Count, 1).End(xlUp).Row

For lColLoop = 25 To 4 Step -1
    Columns(lColLoop).Insert
Next lColLoop

For lRowLoop = lLastRow To 4 Step -2
    For lColLoop = 3 To 48 Step 2
        Cells(lRowLoop, lColLoop).Cut Cells(lRowLoop - 1, lColLoop + 1)
    Next lColLoop
    
    Rows(lRowLoop).Delete
Next lRowLoop

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
End With


End Sub

Open in new window


Thomas
0
 

Author Closing Comment

by:tomfolinsbee
ID: 39905389
Thank you. Worked perfectly first time.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

837 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