This was an interesting challenge. As with just about any coding challenge, there were multiple ways of accomplishing this task. For the challenges so far we’ve seen code ranging three lines long to code 20 lines long, with the outcome of both being identical. We usually would never presume to tell anyone which way is the right way; after all, if it works the way it’s supposed to and if you can understand what the code is doing, well, who are we to say what’s better?
On the other hand, you may come up with a solution that works and makes perfect sense to you, only to then see the solution someone else came up with and think “Wow, that’s better than what I did.”
Chances are you won’t be saying that about the solution we came up with.
Why would we say that? Because we saw some solutions from you guys that we thought were much better than the one we came up with. So what we’re going to do here is take you through our solution, then show you the solution several people submitted that we think is better. You can judge for yourself.
Although, to be honest, you’d be crazy to think that out solution is the better one.
Speaking of which, here is our solution:
Public Sub RowColumnSwap()
Dim i As Integer
Dim j As Integer
Dim objRange As Range
Dim intRows As Integer
Dim intColumns As Integer
Dim s1 As Worksheet
Dim s2 As Worksheet
i = 1
j = 1
'Get references to the data and the two worksheets
Set objRange = Worksheets("Sheet1").UsedRange
Set s1 = Worksheets("Sheet1")
Set s2 = Worksheets("Sheet2")
'Get the number of rows and columns in the table
intRows = objRange.Rows.Count
intColumns = objRange.Columns.Count
'Cycle through the columns
While (i <= intColumns)
'Cycle through the rows within the column
While (j <= intRows)
'Move the values from i,j in Sheet1 to j,i in Sheet2
s2.Cells(i, j).Value = s1.Cells(j, i).Value
j = j + 1
Wend
i = i + 1
j = 1
Wend
'Show Sheet2
s2.Activate
End Sub
We start out by declaring a bunch of variables, then retrieve object references to the used range (the range of cells with data in them) and the two worksheets:
Set objRange = Worksheets("Sheet1").UsedRange
Set s1 = Worksheets("Sheet1")
Set s2 = Worksheets("Sheet2")
Before we can swap rows and columns, we need to find out how many rows and columns we’re working with. We can do that by checking the Count property of the Rows and Columns objects in our range of data:
intRows = objRange.Rows.Count
intColumns = objRange.Columns.Count
The next part might seem a little complicated (which is why it’s not as good as the other solutions). What we’re going to do is turn all the rows into columns, and all the columns into rows. Here’s how we’re going to do that:
1. Loop through the columns on Sheet1 one at a time.
2. Within each column, loop through the rows in that column.
3. Copy the value from each cell at column, row (such as cell 1, 4 – cell A4) in Sheet1 to the corresponding cell row, column (i.e., cell 4, 1 – cell D1) in Sheet2.
Make sense? Let’s walk through it. We start with a While loop that continues while our column counter (i) is less than or equal to the total number of columns we’re copying:
While (i <= intColumns)
Within that column we loop through the rows, continuing through the rows of that column while our row counter (j) is less than or equal to the total number of rows:
While (j <= intRows)
As we just mentioned, our counters contain the column (i) and the row (j) we’re copying. So we simply assign the value of the cell at that location in Sheet1 to the opposite cell in Sheet2, like this:
s2.Cells(i, j).Value = s1.Cells(j, i).Value
We then increment our row counter and continue through the rows. When we’ve reached the last row, we reset our row counter and start on the next column:
i = i + 1
j = 1
The last thing we do is show off our handiwork by making Sheet2 the active worksheet:
s2.Activate
And we’re done. Now, there’s really nothing wrong with this solution: at the very least, you get to practice your nested loops (loops within loops). But once you’ve had all the fun you can handle with loops and nested loops, how about letting Excel do the work for you? Take a look at this revised solution:
Public Sub RowColumnSwap()
Dim objRange As Range
Dim s2 As Worksheet
'Get references to the data and the two worksheets
Set objRange = Worksheets("Sheet1").UsedRange
Set s2 = Worksheets("Sheet2")
objRange.Copy
s2.Activate
s2.Range("A1").PasteSpecial Transpose:=True
'Show Sheet2
's2.Activate
End Sub
Wow, that looks a little simpler, doesn’t it? We start out about the same way we started the first time, declaring a couple variables (although not nearly as many) and retrieving references to the used range in Sheet1 and to the Sheet2 worksheet itself.
Next we call the Copy method on the used range to copy the data from Sheet1 to the Clipboard:
objRange.Copy
After that we call the Activate method on Sheet2 to, well, to make that worksheet the active worksheet:
s2.Activate
The last thing we do is to call the PasteSpecial method:
s2.Range("A1").PasteSpecial Transpose:=True
As you can see, we call this command on a Range object in Sheet2. We use a range consisting just of cell A1 simply because this is the cell where we want to start pasting the data from the Clipboard – the PasteSpecial method will fill in as many cells as it needs from that point.
Notice the parameter we pass to PasteSpecial:
Transpose:=True
If you take a look at the Excel Object Model Reference on MSDN you’ll discover that setting the Transpose parameter to True will “transpose rows and columns when the range is pasted.” And guess what? That’s just exactly what we want to do.
Sure, this wasn’t nearly as much fun as nested While loops. But it was easier.
