Simple Problem - Please Help

Can anyone tell me why this is not working?

This is suppose to check each row on the sheet and if C * E < 3000 then delete that row. I've rewritten this sub many time but cannot get it to work properly.

In advance. Many Thanks!!


Sub Filter_Two()
Dim valPrice As Currency
Dim valQTY As Variant


FinalRow = ActiveSheet.Range("A65536").End(xlUp).Row

For i = 2 To FinalRow
'If ActiveSheet.Range("A" & i).Text = "" Then Exit Sub

valQTY = ActiveSheet.Range("C" & i).Value
valPrice = ActiveSheet.Range("E" & i).Value


If valPrice * valQTY < 3000 Then
Rows(i).Activate
Rows(i).Select
Rows(i).Delete
FinalRow = FinalRow - 1
End If
Next i
End Sub

You need to decrement i if you delete the current row. For Example
Product of Row 2 = <3000
Product of Row 3 = <3000
Your code says delete row 2, now row 3 is row 2 contain product <3000
your code says goto row 3 etc...
So insert this line after Rows(i).Delete
i = i - 1

HTH

Die_Another_Day
mpeplow wrote:
> Can anyone tell me why this is not working?
>
> This is suppose to check each row on the sheet and if C * E < 3000 then
> delete that row. I've rewritten this sub many time but cannot get it to
> work properly.
>
> In advance. Many Thanks!!
>
>
> Sub Filter_Two()
> Dim valPrice As Currency
> Dim valQTY As Variant
>
>
> FinalRow = ActiveSheet.Range("A65536").End(xlUp).Row
>
> For i = 2 To FinalRow
> 'If ActiveSheet.Range("A" & i).Text = "" Then Exit Sub
>
> valQTY = ActiveSheet.Range("C" & i).Value
> valPrice = ActiveSheet.Range("E" & i).Value
>
>
> If valPrice * valQTY < 3000 Then
> Rows(i).Activate
> Rows(i).Select
> Rows(i).Delete
> FinalRow = FinalRow - 1
> End If
> Next i
> End Sub
>
>
> --
> mpeplow
> ------------------------------------------------------------------------
> mpeplow's Profile: http://www.excelforum.com/member.php...o&userid=34812
> View this thread: http://www.excelforum.com/showthread...hreadid=562139

When you are deleting rows you want to work from the bottom up. Otherwise
every delete moves things around on you.

For i = FinalRow to 2 Step -1
'Don't change FinalRow

--
HTH...

Jim Thomlinson


"mpeplow" wrote:

>
> Can anyone tell me why this is not working?
>
> This is suppose to check each row on the sheet and if C * E < 3000 then
> delete that row. I've rewritten this sub many time but cannot get it to
> work properly.
>
> In advance. Many Thanks!!
>
>
> Sub Filter_Two()
> Dim valPrice As Currency
> Dim valQTY As Variant
>
>
> FinalRow = ActiveSheet.Range("A65536").End(xlUp).Row
>
> For i = 2 To FinalRow
> 'If ActiveSheet.Range("A" & i).Text = "" Then Exit Sub
>
> valQTY = ActiveSheet.Range("C" & i).Value
> valPrice = ActiveSheet.Range("E" & i).Value
>
>
> If valPrice * valQTY < 3000 Then
> Rows(i).Activate
> Rows(i).Select
> Rows(i).Delete
> FinalRow = FinalRow - 1
> End If
> Next i
> End Sub
>
>
> --
> mpeplow
> ------------------------------------------------------------------------
> mpeplow's Profile: http://www.excelforum.com/member.php...o&userid=34812
> View this thread: http://www.excelforum.com/showthread...hreadid=562139
>
>

All of this information has been extremly helpful!

THANK YOU!!