I have data that I want to transpose, which should be easy enough. However, There's multiple rows I need to transpose in groups. Each last row in each group has a certain TEXT in there, so I don't know if there is some way to create a range searching for text versus a range? (Would this work? =if(isnumber(search(“TEXT”,A1)). I tried creating a macro, but obviously I need the code to transpose the data down the spreadsheet and not the same data over and over. Any help would be great!

Here is a visual of what I need (Since I cannot format it, it looks funny, and since I'm new I couldn't embed it, so I included a link of a visual of what the rows/column look like/should look like:

From this:

Column A
Row A1
Row A2
Row A3
Row A4
Row A5
Row A6
Row A7
Row A8
Row A9
Row A10
Row A11
Row A12
Row A13

To this:

Col A   Col B   Col C   Col D   Col E
Row A1  Row A2  Row A3  Row A4
Row A5  Row A6  Row A7  Row A8  Row A9
Row A10 Row A11 Row A12 Row A13

Here's the loop code I found:

Sub Test1()
      Dim x As Integer
      Application.ScreenUpdating = False
      ' Set numrows = number of rows of data.
      NumRows = Range("A1", Range("A8”).End(xlDown)).Rows.Count
      ' Select cell a1.
      ' Establish "For" loop to loop "numrows" number of times.
      For x = 1 To NumRows
         ' Insert your code here.

         ' Selects cell down 7 row from active cell.
         ActiveCell.Offset(7, 0).Select
      Application.ScreenUpdating = True
End Sub
  • So, when you say you have multiple rows, do you mean you have multiple cells in Column A?  Because that would make it quite a bit easier, but your terminology is confusing. … … … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete. – Scott Mar 21 '19 at 0:52

Say we start with:

enter image description here

and we want to re-organize into rows with the last item in each row being New. This code:

Sub ReOrg()
    Dim i As Long, j As Long, N As Long, K As Long
    Dim kk As Long
    i = 1
    j = 2
    K = Cells(Rows.Count, "A").End(xlUp).Row

    For kk = 1 To K
        Cells(i, j).Value = Cells(kk, 1).Value
        j = j + 1
        If Cells(kk, 1).Value = "New" Then
            i = i + 1
            j = 2
        End If
    Next kk
End Sub

will produce:

enter image description here

  • OMG I think that's what I need!! Thanks in advance!!! I think you saved my life :-D – a1rapidfab Mar 22 '19 at 0:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.