[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 247

# change number form rows to columns

I have attached an excel sheet
if you look at row2-  from column G to CH are serial numbers.
I transposed row 2 data on row 13 ( which created about 80 records)

I have about 400 records like this- is there anyway access can transpose the numbers?
thank you

SerialNo.xls
0
davetough
• 5
• 5
1 Solution

Commented:
I can write a small Excel macro which can do that for you?

Sid
0

Author Commented:
a excel macro would be fine- if it can do that
0

Commented:
The output will be in Different Sheet. Would that be ok?

Sid
0

Commented:
If you're doing this just once or twice, you could Copy / Paste Special, selecting the "Transpose" option.

If you don't want to use a macro, you can also use the worksheet function TRANSPOSE as an array function (enter the formula by hitting Ctrl+Shift+Enter) which shows up with curly brackets { } around the formula.

0

Author Commented:
different sheet fine
I'll try what you are suggesting pat
0

Commented:
Ok. Please find a sample attached.

Please run the macro "Sample". The out will be generated in a new sheet called "output"

Sid

Code Used

``````Option Explicit

Sub Sample()
Dim ws As Worksheet, ws1 As Worksheet
Dim i As Long, wslastrow As Long, ws1lastrow As Long
Dim r As Long, R1 As Long, R2 As Long

Set ws = Sheets("tblTdaz")
ws1.Name = "Output"

For i = 1 To 6
With ws1
.Cells(1, i) = ws.Cells(1, i)
End With
Next i
ws1.Cells(1, 7) = "SN"

wslastrow = ws.Range("A" & Rows.Count).End(xlUp).Row
ws1lastrow = ws1.Range("A" & Rows.Count).End(xlUp).Row + 1

For i = 2 To wslastrow
ws.Range("A" & i & ":F" & i).Copy ws1.Range("A" & ws1lastrow)
ws.Range("G" & i & ":CH" & i).Copy
ws1.Range("G" & ws1lastrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
R1 = ws1.Range("F" & Rows.Count).End(xlUp).Row
R2 = ws1.Range("G" & Rows.Count).End(xlUp).Row
r = R2
ws1.Range("A" & R1 & ":F" & R1).Copy ws1.Range("A" & R1 + 1 & ":F" & r)
If R1 > R2 Then r = R1 Else r = R2
ws1lastrow = r + 1
Next
End Sub
``````
SerialNo.xls
0

Author Commented:
thank you
0

Author Commented:
if i use this macro on 40 records- will it work? or should I change code in macro any?
thank you
0

Author Commented:
I mean 400
0

Commented:
It will work for 40 records as well till the time they are arranged one below the other leke your 5 rows :)

Sid
0

Commented:
400 as well :)

Sid
0

## Featured Post

• 5
• 5
Tackle projects and never again get stuck behind a technical roadblock.