Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 476
  • Last Modified:

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

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
tomfolinsbee
Asked:
tomfolinsbee
1 Solution
 
nutschCommented:
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
 
tomfolinsbeeAuthor Commented:
Thank you. Worked perfectly first time.
0
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now