Transpose

mmmarks

Active Member
Joined
Jun 4, 2011
Messages
432
Office Version
  1. 2013
HI
i have data like this in Columns
A1 , b1 and c1
1row: P2J QR4 O3J
2Row: X2k YP2
3Row: W09

I want to display like this
In A column
P2J
QR4
O3J
X2K
YP2
W09

thanks
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Try

Code:
Sub Xpose()
Dim LR As Long, LC As Long, i As Long
LR = Columns("A").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Columns("A").Insert
For i = 1 To LR
    LC = Rows(i).Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column - 1
    Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(LC).Value = Application.Transpose(Range("B" & i).Resize(, LC))
Next i
End Sub
 
Upvote 0
Press ALT + F11 to open the Visual Basic Editor and select Module from the Insert menu. Paste the code into the white space on the right. Press ALT + Q to close the code window.

Press ALT + F8, double click on Xpose in the dialog box that pops up.

That's it!
 
Upvote 0
Oh No! Can't it possible with Excel? I'm weak @ VBA

Sure it's possible in Excel. Using:

- very difficult formulas you might not get your head around
- manual cutting and pasting of cells

Just execute VoG's code block and you're done.
 
Upvote 0
Try:
Code:
Sub test()
n1 = Cells(Rows.Count, 1).End(xlUp).Row
n2 = Cells(1, Columns.Count).End(xlToLeft).Column
n = n1 * n2
ReDim vett(n) As String
  For r = 1 To n1
      n3 = Cells(r, Columns.Count).End(xlToLeft).Column
         For c = 1 To n3
             vett(j) = Cells(r, c)
             j = j + 1
Next c, r
For i = LBound(vett) To UBound(vett)
    Cells(i + 1, 1) = vett(i)
Next
Columns("B:C").ClearContents
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,503
Messages
6,179,135
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top