Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-03-03
2
Medium Priority
?
474 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 2000 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…

886 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