How to loop following macro until cell is empty

  • Can anybody please show me how to loop the following macro until a cell is empty in column A of Sheet("Data Sheet Copy"), thanks

    The macro code below selects & copies the first rows of data (including the header) in Sheet("Data Sheet Copy") TransposePaste in an empty row of column A in Sheet("New Sheet"). I then select Sheet("Data Sheet Copy") and delete Row 2 (the row that has been copied). I have tried several code snippets but have been unsuccessful in looping this macro until a cell is empty in column A of Sheet("Data Sheet Copy")


  • Hello and Welcome to the Forum :)


    Your request is not extremely clear ... what do you mean by "until a cell is empty " ?


    Can the following macro be of any help

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • If I'm guessing what you mean correctly, then I don't think you need a Loop.


  • Data Sheet.xlsm

    Thank you everybody for your help.

    I have a table of customers and food products orders. This table changes every will.

    I want to make order lists/invoices for each customer

    I have decided to try the DO loop

    Please find table sample with macro attached

    Has anybody any better solutions, thanks

  • Apparently you want to have a macro using a Do Until Loop approach ...


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Carim

    Changed the title of the thread from “Can anybody please show me how to loop the following macro until a cell is empty” to “How to loop following macro until cell is empty”.
  • Hi ,

    maybe consider

    Code
    1. Option Explicit
    2. Sub TransposePaste_Index_dbArray()
    3. Dim dbArray
    4. With Sheets("Data Sheet Copy")
    5. dbArray = .[A1].CurrentRegion
    6. End With
    7. With Sheets("NewSheet")
    8. .[A1].Resize(UBound(dbArray, 2), UBound(dbArray)) = Application.Transpose(Application.Index(dbArray, Evaluate("Row(1:" & UBound(dbArray) + 1 & ")"), Evaluate("Column(1:" & UBound(dbArray, 2) + 1 & ")")))
    9. End With
    10. End Sub
  • or a reversed transpose