Delete rows w/o criteria
 

I'm making a basic statement error in my program lines
below. Please help.
The company is tracking sales data. Sale rep's send in a
xl sheet monthly. Their data is imported into a master xl
sheet for charting, statics and review. Any row in the
spread sheet that has valid data, row "S" will contain
a "1", Else "0" or "" if it is at then end of the master
sheet.
I need to clean this up so the that sheet contains only
valid data ("1")

What an I missing here

Sub CleanRows()
Dim r As Range
With Worksheets("Sheet1")
For Each r In Range("S2:S" & Cells(Rows.Count, "S").End
(xlUp).Row)
If r.Value = "" Then
Exit Sub
ElseIf r.Value = 0 Then
Rows.Delete
End If
Next
End With

End Sub

Delete rows w/o criteria
 

Try this instead Rick

Sub CleanRows()
Dim r As Long
With Worksheets("Sheet1")
For r = .Cells(Rows.Count, "S").End(xlUp).Row To 2 Step -1
If .Cells(r, "S").Value = "" Then
Exit Sub
ElseIf .Cells(r, "S").Value = 0 Then
.Rows(r).Delete
End If
Next
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"RickK" wrote in message ...
I'm making a basic statement error in my program lines
below. Please help.
The company is tracking sales data. Sale rep's send in a
xl sheet monthly. Their data is imported into a master xl
sheet for charting, statics and review. Any row in the
spread sheet that has valid data, row "S" will contain
a "1", Else "0" or "" if it is at then end of the master
sheet.
I need to clean this up so the that sheet contains only
valid data ("1")

What an I missing here

Sub CleanRows()
Dim r As Range
With Worksheets("Sheet1")
For Each r In Range("S2:S" & Cells(Rows.Count, "S").End
(xlUp).Row)
If r.Value = "" Then
Exit Sub
ElseIf r.Value = 0 Then
Rows.Delete
End If
Next
End With

End Sub

Delete rows w/o criteria
 

That work very well, Thank you
-----Original Message-----
Try this instead Rick

Sub CleanRows()
Dim r As Long
With Worksheets("Sheet1")
For r = .Cells(Rows.Count, "S").End(xlUp).Row To 2
Step -1
If .Cells(r, "S").Value = "" Then
Exit Sub
ElseIf .Cells(r, "S").Value = 0 Then
.Rows(r).Delete
End If
Next
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"RickK" wrote in
message ...
I'm making a basic statement error in my program lines
below. Please help.
The company is tracking sales data. Sale rep's send in a
xl sheet monthly. Their data is imported into a master
xl
sheet for charting, statics and review. Any row in the
spread sheet that has valid data, row "S" will contain
a "1", Else "0" or "" if it is at then end of the master
sheet.
I need to clean this up so the that sheet contains only
valid data ("1")

What an I missing here

Sub CleanRows()
Dim r As Range
With Worksheets("Sheet1")
For Each r In Range("S2:S" & Cells
(Rows.Count, "S").End
(xlUp).Row)
If r.Value = "" Then
Exit Sub
ElseIf r.Value = 0 Then
Rows.Delete
End If
Next
End With

End Sub


.