Macro to Transpose data

[shan]

Hello Everybody,

Require a macro which will copy data and transpose the same in defined column.

Marks available in column C to F need to be Transpose to Column B with respect to the subject.


Input:



Subject
Marks
Maths
English
Fench
German


Maths

50
19
10
11


English







Fench







German







Maths

20
11
30
40


English







Fench







German







Maths

25
40
19
20


English







Fench







German








Output:


Subject
Marks


Maths
50


English
19


Fench
10


German
11


Maths
20


English
11


Fench
30


German
40


Maths
25


English
40


Fench
19


German
20



Thank you

Regards,
Shan

[SamT]

See PasteSpecial(SkipBlanks, Transpose)

[shan]

Thank you Sir for your reply.

I am looking for a macro which will automate this task.

[SamT]

Try to write your own Procedure. Then post the code here and we will help you get it correct.

You have started 23 threads here. It is time that you learned how to write your own code.

[shan]

I agree with you Sam... I am working on a macro and stuck with this transpose. I have tried recording the macro and edit it, I am able to do it only for one line item. I need your help to understand how do I select the next row and paste it.

Below code for your reference:



Range("C2:F2").Select


Selection.Copy


Selection.End(xlToLeft).Select


Range("B2").Select


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _


:=False, Transpose:=True




If you want to have a look at the code which I have created for rest work, if you want I can paste it for your reference.
I am thankful to all expert on this forum because of whom I started writing macro. But still some point I need your expertise.


Thank you.

Regards,
Shan

[SamT]

Let's write down the steps you need to perform


Copy the First Row of Marks
Paste transposed in first unused Cell in Column B
Find the next Row of marks
Copy that Row of marks
Paste transposed in first unused Cell in Column B

Repeat for all Rows of marks.

Note: Avoid the use of"Select" and Activate" when possible

Next determine what Variables you need. Be generous, if you don't use them you can delete that line.


NextRow
PasteCell
FirstMark
LastMark
FirstMarkCol
LastMarkCol
FirstMarkRow
LastMarkRow

If you think of another Variable while you are writing the Procedure, add it to the code. Electrons are cheap.

Note: you can refer to a single Cell on a Sheet by using Row and Column Numbers: Cells(Rownum, ColNum)

Note: You can refer to a large Range by using the first and last Cells in that Range: Range(First, Last)

Note: Next Row is LastRow + 1. LastRow is a very common Variable in Code that refers to Worksheets. Write this line of code in your notebook.
LastRow = Cells(Rows.Count, "A").End(xlUp) 'Where "A" is the column you want to find the last used cell of.Using "End" is like using Ctrl+arrow Key on the Keyboard to navigate around the Worksheet. StartRange.End(Direction).

Note: Offset is like using the Arrow Keys without Ctrl. StartCell.Offset(NumClickDown_Up, NumClicksRight_Left). Use negative numbers for up and left.
Range("A1").Offset(3, 2) ' Moves 3 down, Row 4, and 2 to the right, Column C

Experiment with that a bit
Sub Test()
MsgBox Cells(1,2).Address
MsgBox Cells(1,2).Offset(2,1)Address
MsgBox Range("C3").End(xlUp).Address
MsgBox(Cells(Rows.Count.Columns.Count).Address
MsgBox(Cells(Rows.Count.Columns.Count).End(xlUp).End(xlToLeft).Address

The Range to copy is easy
Range(Cells(FirstMarkRow, FirstMarkCol), Cells(FirstMarkRow, LastMarkCol)).Copy

Where to paste it is easy
Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) PasteSpecial Etc

The next Mark Row is only a bit more confusing. Better add another Variable
NextMarkRow = Range(Cells(FirstMarkRow, FirstMarkCol), Cells(FirstMarkRow, LastMarkCol)).End(xlDown).Row

But that is too many Mark Row variables if you use a Loop in your code, so let's just use MarkRow and set it = to 1 before the loop.

If you are using a Loop, you need to know when to stop the loop. You can stop it after you have processed the LastMarkRow. But which Column To use to check that, since any particular Mark might be missing?
LastMarkRow = Range(Cells(Rows.Count, FirstMarkCol), Cells(Rows.Count, LastMarkCol)).End(xlUp).Row

Any questions about all that? Write some code and post it here for review.

ps: The # Icon will put CODE Tags in your message. Put your code between the CODE Tags. If you copy the code first, as soon as you click the Icon, press Ctrl+V. If you paste the code first, Select the code in the Editor, then click the Icon. If you are typing the code free style, you can type the code tags as needed

[p45cal]

This one makes a few assumptions:
Sub blah()
With Range("A1").CurrentRegion
For Each cll In .Columns(3).SpecialCells(xlCellTypeConstants, 1).Cells
cll.Offset(, -1).Resize(4).Value = Application.Transpose(cll.Resize(, 4).Value)
Next cll
.Columns(3).Resize(, 4).Clear
End With
End Sub

[shan]

Thank you So much Sam...
This will help me to create my own code. Most of my doubts have been cleared.. I will work on the code over this weekend.

Thank you p45cal .... I will take help of your code while preparing my own codes...

Thank you so much to both of you...