Results 1 to 5 of 5

Thread: Transpose A Column Of Data In To A Table

  1. #1
    Junior Member
    Join Date
    May 2013
    Posts
    3
    Rep Power
    0

    Transpose A Column Of Data In To A Table

    i have to transpose the column data in to row.

    in this data the first cell data is of font size 13.5 after that some cells are of font size 10 after that further one cell of font size 13.5 and another are of font 10.....

    I have to do transpose this column data in such away that
    first cell of 13.5 size data will copy in sheet 2 A1, further data of size 10 will copied to B1, C1, D1....till the second 13.5 sized cell came.
    when the 13.5 sized cell came in recognition it will copied to A2, further data of size 10 will copied to B2, C2, D2....till the second 13.5 sized cell came, and continued to the end of the data.....


    i have attached format for desired data transpose as i have described in red font in excel
    Please, help me out for this problem by Macro/VBA code.....

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312886#p312886
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312752#p312752
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312734#p312734
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312727#p312727
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312724#p312724
    https://www.eileenslounge.com/viewtopic.php?f=44&t=40374&p=312535#p312535
    https://www.eileenslounge.com/viewtopic.php?p=312533#p312533
    https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg. 9zYoeePv8sZ9zYqog9KZ5B
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9zYlZPKdOpm
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Attached Files Attached Files
    Last edited by DocAElstein; 03-01-2024 at 03:04 PM. Reason: correction

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    This will collate the data in a new sheet

    Code:
    Sub Consolidator()
    
        Dim rngInput As Range
        Dim lng As Long, lngCol As Long
        Set rngInput = Worksheets("Sheet1").Range("A2:A15000")
        Worksheets.Add After:=Sheets(Sheets.Count)
        For Each rngInput In rngInput
            If IsEmpty(rngInput) Then Exit For
            If rngInput.Font.Bold Then
                lng = lng + 1
                lngCol = 1
            Else
                lngCol = lngCol + 1
            End If
            Sheets(Sheets.Count).Cells(lng, lngCol).Value = rngInput.Value
        Next rngInput
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    May 2013
    Posts
    3
    Rep Power
    0
    thank you for solving....

    Have you tried this code in the attached file..????

    i had a runtime error "1004"
    application defined or object defined


    when i had debug program it has shown yellow background behind the following line of code

    Sheets(Sheets.Count).Cells(lng, lngCol).Value = rngInput.Value

    i think in this line there is some problem...
    Attached Images Attached Images

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Working fine for the sample you've posted. Are you using it on the same file or different?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    May 2013
    Posts
    3
    Rep Power
    0
    ok now it works but it can removes all formatting and hyperlinks....

    please try to keep the formatting after the running code.....

Similar Threads

  1. Replies: 34
    Last Post: 03-13-2015, 02:26 PM
  2. Replies: 2
    Last Post: 04-16-2013, 01:36 PM
  3. Replies: 2
    Last Post: 06-14-2012, 04:10 AM
  4. Transpose data into Rows
    By vikash200418 in forum Excel Help
    Replies: 2
    Last Post: 04-10-2012, 11:02 PM
  5. Replies: 9
    Last Post: 03-13-2012, 01:27 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •