asked on

Excel 2013 VBA - Arrange data from two rows to one

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:

Could you please advise which VBA code could arrange it as having on the 'Target' sheet, so placing each second line to column B?

Thanks in advance,
TwoLinesExample.xlsm

Subodh Tiwari (Neeraj)

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

Subodh Tiwari (Neeraj)

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.

csehz

ASKER

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?

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csehz

ASKER

Thank you that is amazing, it works great

Subodh Tiwari (Neeraj)

You're welcome. Glad to help.
Thanks for the feedback.