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
Reply With Quote
