Search Range for Value, Copy Whole Row if Found

[burger]

Hi,

I'm very new to VBA and have tried to solve this (have read many threads here) in the last couple of days but have not been able. I hope you fine folks are willing to point out where I'm going wrong.

Context:
I've got a spreadsheet with items in rows 5-713 down column B(merged up to cell J) where for each date (Columns K-SP) the item is scored either a 1 or a 0. My goal is to create a list at the bottom of the worksheet that contains all items which have gone from 1 to 0. To start, I've simply been trying to get my "generate list" button to copy all rows with a 0 in them to the bottom, figuring I would tweak it later to do exactly what I wanted. I've tried several things and gotten several different errors.

Here is my code so far, and (I think) the closest I've come.

Private Sub CommandButton1_Click()

Dim y As Integer, z As Integer

y = 5
z = 714

Do Until y = 713
    If Range("By:SPy").Value = 0 Then Range("By:SPy").Copy Range("Bz")
    y = y + 1
    z = z + 1

Loop
   

End Sub

The error I get with this attempt is "out of memory". Several other attempts have given me "method 'range of object' _Worksheet failed".

Any help to figure out this first step, or even how to have it scan for 1's that turn to 0's would be wonderfully appreciated. I'm sure I'm missing something simple and hope you guys can forgive my ignorance of all things coding.

Thanks!

 

[burger]

Sorry to double post, but I was checking up on the posting guidelines and it looks like I left out a bit of information that is appreciated here.

This is in Excel 2010, on Windows.

Here is a sample table to represent what I tried to describe in the 'context' section above. Sorry it is not as pretty as some of the ones around here, I could not quite figure out how with the in-built tables:

1	A-J	K	L	M	N	O	P	Q	...	SP
2	info	date	date	date	date	date	date	date	...	date
3	info	initials	initials	initials	initials	initials	initials	initials	...	initials
4	info	age	age	age	age	age	age	age	...	age
5	item 1	1	1	1	1	0	1	1	...	1
6	item 2	1	1	1	1	1	1	1	...	1
7	item 3	1	1	1	1	1	1	1	...	1
...	...	...	...	...	...	...	...	...	...	...
713	item 713	0	0	0	0	1	1	0	...	1

<tbody>
</tbody>