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

    Transposing repeatedly (97 SR2)

    I have a list of 1000 addresses that was compiled completely vertically. I want to take each address and pull the line into a separate column. For example, if I have a 5-line address, I want to move each line to Column A, Column B, Column C, Column D, and Column E. I want to repeat this for each address. In an ideal situation, I'd like to avoid blank rows between the addresses, but I'd rather have a quick and reliable macro that left the blank lines. I've attached an example workbook of what I have and what I'd like to end up with.
    Attached Files Attached Files

  2. #2
    New Lounger
    Join Date
    Jan 2001
    Location
    Brooklyn Park, Minnesota, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transposing repeatedly (97 SR2)

    What is the best way to get this code into a macro? When I go to Tools|Macros, the "create" button is greyed out. Will it work if I paste this code into a previously recorded macro? I tried that method and ran the macro. It blinked repeatedly on cell A1 and nothing happened.

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

    Re: Transposing repeatedly (97 SR2)

    Sorry, I forgot to mention that the code will build the transposed list on Sheet2. Did you look at Sheet2 after running the macro?

    I have attached your example workbook with the code included and have added a small change to keep it from flashing while it is running.
    Attached Files Attached Files
    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Jan 2001
    Location
    Brooklyn Park, Minnesota, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transposing repeatedly (97 SR2)

    Oops! When I checked Sheet 2, there was exactly what I needed! Thank you so much! You have saved me hours of work!

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

    Re: Transposing repeatedly (97 SR2)

    The VBA routine below should do what you want:

    <pre>Public Sub FormatAddresses()
    Dim lLastRow As Long, I As Long, J As Long, K As Long
    lLastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row - 1
    J = 0
    K = 0
    For I = 0 To lLastRow
    If Worksheets("Sheet1").Range("A1").Offset(I, 0).Value = "" Then
    J = J + 1
    K = 0
    Else
    Worksheets("Sheet2").Range("A1").Offset(J, K).Value = _
    Worksheets("Sheet1").Range("A1").Offset(I, 0).Value
    K = K + 1
    End If
    Next I
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

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