Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Repeat a cell 33 times (excel XP, SR1)

    I have in column A, 1200 rows of text. I need to repeat each one of these 33 times, all in the same column.

    What I need to end up with is each entry in Column A ,in 33 rows, then the next entry in column A in the next 33 rows, until the end.

    Thanks in advance, Kent DeCook

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Repeat a cell 33 times (excel XP, SR1)

    Number each of the rows (1 to 1200) in a blank column.
    Copy the numbered columns and col a for all 1200 rows underneat the corrent region. Repeat 33 times Now you have them each 33 times though all separate.
    SORT the region by the numbered column and you will get 33 of item 1, then 33 of item 2, etcSteve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repeat a cell 33 times (excel XP, SR1)

    See if this VBA code will do what you want:

    <pre>Public Sub Copy33()
    Dim lLastRow As Long, I As Long, J As Long
    lLastRow = ActiveSheet.Range("A65536").End(xlUp).Row - 1
    For I = lLastRow To 0 Step -1
    ActiveSheet.Range(ActiveSheet.Range("A1").Offset(I + 1, 0), _
    ActiveSheet.Range("A1").Offset(I + 32)).EntireRow.Insert (xlShiftDown)
    For J = 1 To 32
    ActiveSheet.Range("A1").Offset(I + J).Value = ActiveSheet.Range("A1").Offset(I, 0).Value
    Next J
    Next I
    End Sub
    </pre>

    Legare Coleman

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts

    Re: Repeat a cell 33 times (excel XP, SR1)

    I like Steve's method - it is simple, elegant, has a nice logic to it and is probably faster to implement.

    Legare, I love your loops and have learned much from your coding posts.

    zeddy

  5. #5
    New Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repeat a cell 33 times (excel XP, SR1)

    Excellent, Thanks a ton...

Posting Permissions

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