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

  • Hello!


    I'm VERY new to VBA, like, this is my first time trying to use it new. I have a sheet in excel (called Fedex) that is auto populated from a different sheet (invoice). I would like to copy entire rows from the fedex sheet that have a nonzero value in column A, such that in the new sheet I have a simple table of all items that have nonzero quantities. I have a module that is currently just copying the entire list into a new sheet, 0 rows included. Can someone please help me??


    This is what I have currently (mostly just a copy from something online with some edits I tried to add to get it working):



    Thank you!

  • Welcome to Ozgrid!


    There is a conflict between your thread title and what you say in the thread. The title implies that entire rows might be empty, but your description says that column A might have a zero.


    Might be best if you were to attach your workbook.

    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.

  • Your sample file contains formulas that are liked to another workbook on your system. Obviously those links do not work for me when I open the file.


    Can you make a sample file that replaces all the formulas with values (easiest way is to add a new sheet, select everything on the "COPY FROM" sheet and paste value only onto the new sheet).


    Then clearly indicate which rows need to be copied to the "FINAL" sheet.


    Do you really mean copy/paste not cut/paste? If copy/paste then the data will remain in the COPY FROM sheet and get duplicated in the FINAL sheet when the macro runs next time!

    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.

  • Sorry for the poor explanations/examples. Again I am very new to this. I have adjusted the example worksheet and reattached it for reference. To clarify. The rows highlighted in green in the "COPY FROM" sheet are to be copied, NOT cut, and then transferred into the "Final" sheet such that the final sheet is a smaller table. The criterion for the rows to be copied is that there is a nonzero value in Column A. Every row with a natural number in A should get copied, every row with a 0 in A should be left behind.


    As an add on, the number of rows in the "COPY FROM" sheet will eventually get longer, so expanding the range to greater than A21 would be useful for me.


    Thank you for taking the time to help a novice out!

  • OK, sample file is fine now.


    One last question, when the data is copied to the FINAL sheet should it be added to data that is already in that sheet or replace any data in the FINAL sheet?

    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.

  • Try this.


    Note that your sample FINAL sheet had a trailing space in the tab name ("FINAL " instead of "FINAL"), make sure you remove that trailing space.

    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.

  • You're welcome.

    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.

  • Hello again,


    I hope its alright I post on this forum again. I have another question and you were so helpful last time. I am attempting to write another Macro that will copy certain columns form one worksheet into a specific column in another worksheet. The macro basically works, however it doesn't copy the values for 2 of the columns in the destination sheet. I will attach my workbook, as well as the code that I am running.


    Here is my code:


    As you can see, I have just repeated the same step over and over. It worked for the copy into columns C, D, and F. However, columns E and G did not copy. I don't know why it would only have worked for a couple of the columns. Can you please help me again?

  • Hi..


    You should really create a new thread.. it helps when others are searching for the same solution..


    Your code wasn't getting all the data you wanted because you were using xlDown instead of Xlup..


    In any case.. try this:


    Code
    Sub Copy_Columns()
    Dim x
     x = Sheets("HomeWork").Range("A4:H" & Sheets("HomeWork").Range("A" & Rows.Count).End(xlUp).Row)
     Sheets("NavInv").[C2].Resize(UBound(x, 1), 5).Value = Application.Index(x, Evaluate("row(1:" & UBound(x) & ")"), Array(1, 3, 4, 2, 5))
    End Sub


    btw.. Also post your code within the code tags.. it's the little hash symbol on the edit bar..

  • Try this.


    Note that your sample FINAL sheet had a trailing space in the tab name ("FINAL " instead of "FINAL"), make sure you remove that trailing space.

    Hi there


    thanks so much for posting this. This is my first time using Visual Basic and your code works well. Except, I have one column that is account numbers and so those lines keep being copied to the new sheet. Is there a minor change I can make to deflect that column please? In my case it column D (4th column).


    Many thanks for any help you can give.

  • The simplest way is probably to copy across all the data then delete column 4. Try this


    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


    many thanks for coming back so fast. Unfortunately that won't work for me in this instance. I have a table with multiple rows and columns, i want to copy across just the rows that contain certain complete columns (containing data), but i also need the account code that is in the D row. So the VBA needs to ignore the account code column for the calculation but carry the account code into the new sheet.


    the way you have suggested would mean the rows would be selected but without the account code, and that would need to be entered manually afterwards.


    is the a line i can add just ignoring a specific column?


    many thanks for your help and ideas

  • Hello,


    To make sure the suggested solution is exactly adapted to your specific situation .. why don't you attach a sample file ...;)

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

  • thanks for the suggestion - the first tab is the amalgamated data from other sheets.


    i want to just take the non zero rows (the ones with a unit price) into the export sheet (xero export)


    at the moment the VBA sees the account codes and the amount columns as data, and copies them over (ie copies all the rows rather than just the ones with a unit price).


    many thanks for your help :)

  • Try this


    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.

  • I would suggest automating AutoFilter


Participate now!

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