Copy row only if one cell's value is not 0 [Solved/Closed]

victhor1977 1 Posts Thursday April 14, 2016Registration date April 14, 2016 Last seen - Apr 14, 2016 at 10:34 AM - Latest reply: vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen
- Apr 18, 2016 at 03:13 AM
I would like to copy from a sheet to another only the rows in wich a cell ex.A1 has a value different from zero or blank.
Thanks a lot!
See more 

3 replies

vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Apr 16, 2016 at 03:37 AM
0
Thank you
Hello Victhor1977,

The following code may do the task for you:-


Sub CopyStuff()

Application.ScreenUpdating = False
Application.DisplayAlerts = False


     Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter 1, "<>" & "", xlAnd, "<>" & 0#
     Range("A2:I" & Cells(Rows.Count, 9).End(xlUp).Row).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
     Range("A2:I" & Cells(Rows.Count, 9).End(xlUp).Row).Delete
     [A1].AutoFilter
    
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Sheet2.Select

End Sub


Any rows of data that do not have a blank cell in Column A or a zero value in Column A will be transferred to sheet2 using the above code. The code will also delete the same rows from sheet1 once they have been transferred to sheet2.

Following is the link to my test work book for you to peruse:-

https://www.dropbox.com/s/fnu8hekouhfsc1h/Victhor1977%28Copy%20on%202%20criteria%20using%20autofilter%29.xlsm?dl=0

Click on the Transfer button to see the code at work. My test work book has nine columns (A - I). Change the references in the code to suit your actual work book including the column number (shown as 9 in the above code: I=9, J=10 etc.).

Test the code in a copy of your work book first.

I hope that this helps.

Cheerio,
vcoolio.
Thanks,
Worked like a charm!
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Apr 18, 2016 at 03:13 AM
0
Thank you
Hello Victhor,

Excellent! I'm glad that I was able to help.

Cheerio,
vcoolio.