Use excel transpose or similar


We have a super big excel with over 1,200 rows 255 columns (rows=modems, columns=ports).  We have been using excel transpose to just have 2 columns modems and ports (see below example).

Modem   Port-01 Port-02 Port-03
-------- ------ ------- --------
NetGear   4672   555    SMS
Arris     non     x     SMS

Open in new window

We want to convert the data into 2 columns as follows:

Modem	Ports
NetGear	4672
NetGear	555
NetGear	SMS
Arris	non
Arris	x
Arris	SMS

Open in new window

We have used excel transpose, but we have to do this one row by row.

Is there a way for using transpose or another  function so we can get the result we are  looking for?
(attached sample files of current and trying to accomplish)
Who is Participating?
Ejgil HedegaardConnect With a Mentor Commented:
The statement assigns the sheet to a variable.
Doing so makes coding easier, because IntelliSense shows the options for a worksheet, so you don't have to remember, makes addressing the sheet easier, and avoids some errors.

Both columns and rows are counted, for the region with the cell A1.
A region is a range surrounded by empty cells.
So put your real data in A1 down and across, and it will work.

I forgot to turn off screen updating (speeds up processing), so here is the code with that.

Option Explicit

Sub ColumnsToRows()
    Dim wsIn As Worksheet, wsOut As Worksheet
    Dim rwIn As Long, rwOut As Long, colIn As Integer
    Set wsIn = ThisWorkbook.Worksheets("Sheet2")
    Set wsOut = ThisWorkbook.Worksheets.Add
    Application.ScreenUpdating = False
    wsOut.Range("A1:B1") = Array("Modem", "Ports")
    rwOut = 1
    For rwIn = 2 To wsIn.Range("A1").CurrentRegion.Rows.Count
        For colIn = 2 To wsIn.Range("A1").CurrentRegion.Columns.Count
            rwOut = rwOut + 1
            wsOut.Cells(rwOut, 1) = wsIn.Cells(rwIn, 1)
            wsOut.Cells(rwOut, 2) = wsIn.Cells(rwIn, colIn)
        Next colIn
    Next rwIn
End Sub

Open in new window

rayluvsAuthor Commented:
(oops! forgot to attachment)
Ejgil HedegaardCommented:
Try attached, press button to run.
rayluvsAuthor Commented:
Thanx! I thought that there was a way with transpose, but thank you, VBA is the way.

Prior closing the question,
- Can you give a brief on the statement "Set wsIn = ThisWorkbook.Worksheets("Sheet2")"
- Also, in the real excel we have over 200 columns, where do I increase the value to consider converting those columns also to rows?
rayluvsAuthor Commented:
Thank you!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.