Columns to Lines

Basic Exercise

Statement

Create a subroutine that will transfer the contents of a column into a line of another worksheet.

Suggested Concepts:

Dim - Declares a variable.
End with .Row - Find the last row with data.
For Each - Repeats a group of statements for each element in a collection.
If Else - Conditionally executes a group of statements, depending on the value of an expression.
For Next - Repeats a group of statements a specified number of times.

Remember that in most cases there is more than one way to solve the exercise.

Hint: Create two loops, one within the other, that will invert the cells position from (i, j) to (j, i).

Solved Exercise

Exercise Solution

We start by declering the variable

Dim i As Integer
Dim j As Integer
Dim LastLine As Long
Dim LastColumn As Long
Dim Ws As Worksheet
Dim Verif As Boolean

We create a For Each loop to verify if there is a worksheet in the file that is already labeled "Inverted". If that is the case, the boolean variable (Verif) output is true
```
For Each Ws In Worksheets
    If Ws.Name = "Transpose" Then
        Verif = True
    End If
Next
```
If the output of the variable Verif is false then an If Else conditional will create a new worksheet labeled "Transpose" in the file. With this procedure, we avoid the creation of several new "Transpose" worksheets if we need run the macro for a second time in the same file
```
If Verif = True Then
Else
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Transpose"
End If
```

Now that the output worsheet is defined, we locate the last entry (line and column) on the Sheets ("SVBA_Solution") and declare the following variables

LastLine = Sheets("SVBA_Solution").Cells(Rows.Count, 2).End(xlUp).Row
LastColumn = Sheets("SVBA_Solution").Cells(2, Columns.Count).End(xlToLeft).Column

We create two For Next loops, one within the other. The first loop will run from the first line with data until the last (i) and the second loop will run from the first column with data until the last (j)
```
For i = 2 To LastLine
    For j = 1 To LastColumn
```
Finally, we invert the position of the cells. Every line on the Sheets ("SVBA_Solution") worksheet will become a column on the Sheets ("Transpose") worksheet and every column a line
```
        Sheets("Transpose").Cells(j, i) = Sheets("SVBA_Solution").Cells(i, j)
    Next j
Next i
```

Consolidated Answer

    Sub Solution()

    Dim i As Integer
    Dim j As Integer
    Dim LastLine As Long
    Dim LastColumn As Long
    Dim Ws As Worksheet
    Dim Verif As Boolean

    For Each Ws In Worksheets
        If Ws.Name = "Transpose" Then
            Verif = True
        End If
    Next

    If Verif = True Then
    Else
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Transpose"
    End If

    LastLine = Sheets("SVBA_Solution").Cells(Rows.Count, 2).End(xlUp).Row
    LastColumn = Sheets("SVBA_Solution").Cells(2, Columns.Count).End(xlToLeft).Column

    For i = 2 To LastLine
        For j = 1 To LastColumn
             Sheets("Transpose").Cells(j, i) = Sheets("SVBA_Solution").Cells(i, j)
        Next j
    Next i

    End Sub

Show Answer

Download exercise Download answer

SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

INTRODUCTION

BASIC

INTERMEDIATE

ADVANCED