none
How to paste a row from one sheet onto the next available row in a second sheet in excel using VBA? RRS feed

  • Question

  • I am trying to clean data in excel using VBA.  In my first sheet, I have my raw data.  In one column on that first sheet, a number is either zero or not zero.  I would like to transfer the row corresponding to any nonzero entry in said column onto another sheet.  Additionally, I would like to place the row onto the next empty row in the new sheet.  How would you go about this?

    I am new to VBA, can you please comment any code suggestions so it's understandable to me, as I am trying to learn.

    Monday, December 7, 2015 5:31 AM

Answers

  • I am assuming that you are using a version of Excel from 2007 or later.

    Turn on AutoFilter

    Select the DropDown for the column

    Select Number Filter (above the list of available filters for the column)

    Select "Does not equal" and enter a zero in the field.

    Select the visible data and then select "Find & Select" on the Home ribbon.

    Select "Go To Special"

    Select "Visible cells only" and OK.

    Place the cursor over the selection and Copy and then go to the first cell of the destination and Paste.


    Regards, OssieMac

    • Proposed as answer by André Santo Monday, December 7, 2015 1:30 PM
    • Marked as answer by jsb348 Tuesday, December 8, 2015 2:54 AM
    Monday, December 7, 2015 8:22 AM

All replies

  • I am assuming that you are using a version of Excel from 2007 or later.

    Turn on AutoFilter

    Select the DropDown for the column

    Select Number Filter (above the list of available filters for the column)

    Select "Does not equal" and enter a zero in the field.

    Select the visible data and then select "Find & Select" on the Home ribbon.

    Select "Go To Special"

    Select "Visible cells only" and OK.

    Place the cursor over the selection and Copy and then go to the first cell of the destination and Paste.


    Regards, OssieMac

    • Proposed as answer by André Santo Monday, December 7, 2015 1:30 PM
    • Marked as answer by jsb348 Tuesday, December 8, 2015 2:54 AM
    Monday, December 7, 2015 8:22 AM
  • Thanks!  It works!
    Tuesday, December 8, 2015 2:55 AM