Solved

Excel 2013 VBA - Arrange data from two rows to one

Posted on 2016-08-10
6
47 Views
Last Modified: 2016-08-10
Dear Experts,

Could you please have a look to the attached file on 'Sheet1', basically I have data in the following format so being in separate lines:
Data in two lines
Could you please advise which VBA code could arrange it as having on the 'Target' sheet, so placing each second line to column B?
Data arranged in one line
Thanks in advance,
TwoLinesExample.xlsm
0
Comment
Question by:csehz
  • 4
  • 2
6 Comments
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41750333
You may try something like this......

Sub ReArrangeData()
Dim sws As Worksheet, dws As Worksheet
Set sws = Sheets("Sheet1")
Set dws = Sheets("Target")
dws.Cells.Clear
On Error Resume Next
sws.Columns("A:A").SpecialCells(xlCellTypeConstants, 1).Copy dws.Range("A1")
sws.Columns("A:A").SpecialCells(xlCellTypeConstants, 2).Copy dws.Range("B1")
dws.Activate
End Sub

Open in new window

0
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41750342
Try the suggested code on your sample workbook as the code assumes that there are two sheets called Target and Sheet1 in the workbook where Sheet1 contains the raw data. If the sheet names are different, please change them in the code as per the actual sheet names.
0
 
LVL 1

Author Comment

by:csehz
ID: 41750357
Thank you it works of course,

could you please advise how to modify the code if the A1,A3,A5 etc cells are not number but also text?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 29

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41750382
In that case, try this.....
Sub ReArrangeDataV2()
Dim sws As Worksheet, dws As Worksheet
Dim lr As Long
Application.ScreenUpdating = False
Set sws = Sheets("Sheet1")
Set dws = Sheets("Target")
lr = sws.Cells(Rows.Count, 1).End(xlUp).Row
dws.Cells.Clear
sws.Columns(1).Insert
sws.Range("A1:A" & lr).Formula = "=IF(MOD(ROW(),2)=1,NA(),2)"
sws.Range("A:A").SpecialCells(xlCellTypeFormulas, 16).Offset(0, 1).Copy dws.Range("A1")
sws.Range("A:A").SpecialCells(xlCellTypeFormulas, 1).Offset(0, 1).Copy dws.Range("B1")
sws.Columns(1).Delete
dws.Activate
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:csehz
ID: 41750400
Thank you that is amazing, it works great
0
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41750431
You're welcome. Glad to help.
Thanks for the feedback.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

772 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