Copying rows from one sheet to another excluding "empty" rows

  • hi KJBox


    it works but it doesn't carry across the amounts for unit price - it makes them all 1


    what should i change so it carries the unit price across please?


    many thanks

  • @ hjulian72


    You gave earlier the indication to skip Column D ... which KjBox ;) did respect in his code ...


    If you know how to handle Arrays ... you can separate out this column and modify as follows :

    Code
    1. ' Deal with First 2 Columns
    2. For ii = 1 To 2
    3. y(ii, iii) = x(i, j)
    4. Next ii
    5. ' Deal with Third Column
    6. If ii = 3 Then y(ii, iii) = x(i, ii + 1)
    7. ' Deal with the Last three Columns 5 to 7
    8. For ii = 5 To UBound(x, 2)
    9. y(ii - 1, iii) = x(i, ii)
    10. Next ii


    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:)

  • I would suggest automating AutoFilter


    hi Roy


    I'm sorry I don't understand. Do I add this to the other code or use in place of the other code?


    many thanks


    Julian


  • Hello Julian,


    KjBox' s macro is attached to your test file ...:)


    His Copy macro is using Arrays for maximum efficiency ...


    By the way ... standard macros have to be stored in a standard module ... NOT in the worksheet's module ....;)


    Hope this will help

    :)

    Files

    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:)

  • Once you have tested the macro ... feel free to share your comments

    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:)

  • Quote

    it works but it doesn't carry across the amounts for unit price - it makes them all 1

    I am totally confused, your example shows exactly what my macro will produce. You ask that column D values should not be included in the result, column D is Unit Price, now you are saying that all Unit Prices in the result are 1 (in fact Amount is 1) and you now want Unit Price included in the result.


    Why don't you attach a file where you manually create the Xero Export sheet so we know exactly what the required result should be.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • thanks so much for your help - its worked


    however, there is no amount column so I'm having to add back in an amount column (xero needs an amount and a unit price)


    ive added a separate macro (in a new module) on the export sheet which adds a column with the heading "amount" but I can't work out how to add the number 1 to the column below


    this is what ive added:


    Code
    1. Sub UpdateColumns()
    2. Dim LastRow As Long
    3. LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    4. Range("C1").EntireColumn.Insert
    5. Range("C1").Value = "amount"
    6. Range("C2").Copy
    7. Range("C2:C" & LastRow).PasteSpecial (xlPasteAll)
    8. End Sub


    is there a way i can add this to the original macro so it takes the populated lines to the export sheet and automatically adds the amount column containing the number 1?


    many thanks

  • I assume you mean Carim's modification of my code works for you.


    Now you are saying you need the Amount column included in the result data? Is that correct?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • hi kjbox


    your code has been briliiant thank-you and its helped my understand this a bit more


    ive always needed it as the accounts import requires 1 x XXXX for each line


    i couldnt find a way of copying the unpopulated lines across but keeping the amount column - so ive added a separate macro on the exported data page to add that column but i cant work out how to add "1" to each cell in that column