rows to columns and colums to rows

  • Thread starter Thread starter Bravo
  • Start date Start date
B

Bravo

Hi,

I am New to excel programing in VB,
I want to know the code how to change the row to colume and colume to
row

and can i can i know where i learn online for programing for excel.



for eg if i have :
abc 1
abc 2
abc 3


xyz 2
xyz 3
xyz 5


hgf 3
hgf 4
hgf 3


and i have a button call CHANGE


when i click on the button CHANGE


abc 1 2 3
xyz 2 3 5
hgf 3 4 3


and


when i have something like this


abc 1 2 3
xyz 2 3 5
hgf 3 4 3


and i have a button call CHANGE


when i click on the button CHANGE


abc 1
abc 2
abc 3


xyz 2
xyz 3
xyz 5


hgf 3
hgf 4
hgf 3


hope you have understood what i ment...


like vice versa


Thank you in advance


Regards
 
You can try Copy, and Paste Special and click the Transpose checkbox. This
may not work for your situation though.

To find VBA Programming Resources online, I would just Google VBA Tutorials
or VBA Programming, etc. You should get lots of resources.

Hope this helps.

Keith
 
Hello,

I've written something similar to what you want, I'll try to get the
code for you shortly.

--JP
 
Hello,

I've written something similar to what you want, I'll try to get the
code for you shortly.

--JP




- Show quoted text -
Click to expand...

JP

can you please share the code with me
 
Some time ago, I had the same question on the rows to columns on reference
(your 2nd scenario). Here was the response I received unfortunately I don't
remember from whom. I did change it a bit to create a new sheet but this
should work provided your data is within column A:K if not change the range
to match your case.

Public Sub ProcessData2()
Dim cColumns As Long
Dim i As Long
Dim iLastRow As Long
Dim iLastCol As Long
Dim cMaxCols As Long
Dim cell As Range
Dim sh As Worksheet

Columns("A:K").Select
Columns("A:K").Copy
Sheets.Add
Range("A1").Select
ActiveSheet.Paste

With ActiveSheet

cColumns = .Cells(1, .Columns.Count).End(xlToLeft).Column
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If .Cells(i, "A").Value = .Cells(i - 1, "A").Value Then
iLastCol = .Cells(i, .Columns.Count) _
..End(xlToLeft).Column
..Cells(i, "B").Resize(1, iLastCol - 1).Copy .Cells(i - 1, cColumns + 1)
If iLastCol + cColumns > cMaxCols Then _
cMaxCols = iLastCol + cColumns - 1
..Rows(i).Delete
End If
Next i

'now add headings
For i = cColumns + 1 To cMaxCols Step cColumns - 1
..Range("B1").Resize(, cColumns - 1).Copy .Cells(1, i)
Next i

End With

MsgBox "Run Complete"

End Sub

Now I have the same question as your 1st scenario (columns to rows).
 
Back
Top