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

  • @ 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 :

               ' Deal with First 2 Columns
                   For ii = 1 To 2
                       y(ii, iii) = x(i, j)
                   Next ii
               ' Deal with Third Column
                   If ii = 3 Then y(ii, iii) = x(i, ii + 1)
               ' Deal with the Last three Columns 5 to 7
                   For ii = 5 To UBound(x, 2)
                       y(ii - 1, iii) = x(i, ii)
                   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


  • 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


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

    Sub UpdateColumns()
    Dim LastRow As Long
    LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    Range("C1").Value = "amount"
    Range("C2:C" & LastRow).PasteSpecial (xlPasteAll)
    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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!