copying values from a range that varies in size to a new worksheet RRS feed

  • Question

  • I have a data set that is about 200 rows down and 1,500 columns across. Each row has up to 1,500 entries but the number varies. I want to copy and paste the values onto a separate worksheet into two columns, with the first number being the row number and the second being one of the (up to 1,500) values. For example, if row 1 had 78 values going across in columns, I would want the copy and pasted data to appear with a 1 in the first column and the first column value in the second column, then move down a row and have a 1 in the first column and the second column value in the second column all the way through the 78 values. Then I would want to loop through the second row and repeat the process until all rows had been processed. Any tips to write a sub that would loop through and do this?
    Monday, July 23, 2012 9:26 PM


  • Try this macro:

    Sub Transform()
        Dim wshIn As Worksheet
        Dim wshOut As Worksheet
        Dim s As Long
        Dim m As Long
        Dim c As Long
        Dim n As Long
        Dim t As Long
        Application.ScreenUpdating = False
        Set wshIn = ActiveSheet
        Set wshOut = Worksheets.Add(After:=wshIn)
        m = wshIn.Cells.Find(What:="*", SearchOrder:=xlByRows, _
        For s = 1 To m
            n = wshIn.Cells(s, wshIn.Columns.Count).End(xlToLeft).Column
            For c = 1 To n
                If wshIn.Cells(s, c).Value <> "" Then
                    t = t + 1
                    wshOut.Cells(t, 1) = s
                    wshOut.Cells(t, 2) = wshIn.Cells(s, c).Value
                End If
            Next c
        Next s
        Application.ScreenUpdating = True
    End Sub

    Regards, Hans Vogelaar

    Monday, July 23, 2012 9:48 PM