rows to columns and colums to rows

[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

 

[Guest]

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

 

[JP]

Hello,

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

--JP

 

[Steve G]

Bravo--

You might try the web sites of Gary Radley and Martin Green for VBA
training. There web sites are:

http://www.users.bigpond.net.au/gradley/vbatutor/index.html

http://www.fontstuff.com/vba/index.htm

Steve G

 

[Bravo]

Hello,

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

--JP




- Show quoted text -

JP

can you please share the code with me

 

[Guest]

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).