1. Registered User
Join Date
Dec 2015
Posts
2

Hi there.
This is outside of my scope, but I'm trying to do it w/o much luck. Hopefully someone here can help

I have an excel export for which row 1 has 3 values (columns)

A B C
APT-111 12222 111.47

I need it to display like this

A B
APT-111 12222
APT-111 111.47

This would be for the entire sheet.

Any help would be greatly appreciated.

"Hi Just a little routine that my give you some ideas Code: ```Option Explicit Sub ReArrange() Dim iRow As Integer Dim i As Integer Dim Data() As String iRow = 1 i = 1 Do Until Cells(iRow, 1) = "" ReDim Preserve Data(1 To 3, 1 To i) Data(1, i) = Cells(iRow, 1) Data(2, i) = Cells(iRow, 2) Data(3, i) = Cells(iRow, 3) iRow = iRow + 1 i = i + 1 Loop If i > 1 Then ' i IS GREATER THAN ONE IF DATA FOUND 'REMOVE THE NEXT LINE IF YOU WANT TO REPLACE THE DATA IN THE EXISTING SHEET Sheets.Add After:=Sheets(Sheets.Count) iRow = 1 For i = 1 To UBound(Data, 2) Cells(iRow, 1) = Data(1, i) Cells(iRow, 2) = Data(2, i) Cells(iRow, 3) = "" iRow = iRow + 1 Cells(iRow, 1) = Data(1, i) Cells(iRow, 2) = Data(3, i) Cells(iRow, 3) = "" iRow = iRow + 1 Next i End If End Sub``` There is info on arrays and dimensioning them in Excel help. HTH MTB"

3. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Hi

Just a little routine that my give you some ideas
Code:
```Option Explicit

Sub ReArrange()
Dim iRow As Integer
Dim i As Integer
Dim Data() As String

iRow = 1
i = 1
Do Until Cells(iRow, 1) = ""
ReDim Preserve Data(1 To 3, 1 To i)
Data(1, i) = Cells(iRow, 1)
Data(2, i) = Cells(iRow, 2)
Data(3, i) = Cells(iRow, 3)
iRow = iRow + 1
i = i + 1
Loop

If i > 1 Then ' i IS GREATER THAN ONE IF DATA FOUND

'REMOVE THE NEXT LINE IF YOU WANT TO REPLACE THE DATA IN THE EXISTING SHEET

iRow = 1
For i = 1 To UBound(Data, 2)
Cells(iRow, 1) = Data(1, i)
Cells(iRow, 2) = Data(2, i)
Cells(iRow, 3) = ""
iRow = iRow + 1
Cells(iRow, 1) = Data(1, i)
Cells(iRow, 2) = Data(3, i)
Cells(iRow, 3) = ""
iRow = iRow + 1
Next i
End If
End Sub```
There is info on arrays and dimensioning them in Excel help.

HTH

MTB

4. Registered User
Join Date
Dec 2015
Posts
2
Thank you so much, that's exactly it
You rock!