nuganx

How to delete an entire row in Excel 2007 if value of a cell isnt equal to some text?

Greetings community,

I am new on this forum. I am learning Excel 2007 coding and now I have to find a way to delete an entire row if this entire row has a cell value not equal to some text. I found that a VBA macro would be the only way to make it automatically, since a formula is not allowed to delete a row.


Here is the link to download the spreadsheet:

http://www.megaupload.com/?d=76XX65RN


The file has been supplied by my boss without the column "O"; I coded "O" cells by myself without any major problem (else than language problems since I am french but used to code in english, and strangely a french install of Excel does not allow me to code in english, which is weird ). This represents a monthly report of activated gift cards for a business and purchases done with them. My boss wanted the spreedsheet to automatically calculate the remaining balance of cash available on each gift card, thus it had to detect each card serial number activity AFTER some ascending number sorting on column "E".

Everything works fine up to here, as you can see. But now, my boss wants each row that does not result in "Il reste 0$ en crédit sur cette carte" in column "O" to be automatically deleted, in order to get ONLY a list of cards that no more have cash in bank in order to proceed to their de-activation.

So I opened up the VB editor for the first time, and after some Google searches on the topic I wrote the following:



Sub remove()
Dim sr1 As Long
For sr1 = Sheets("Sheet1").Range("04").CurrentRegion.Rows.Count To 200 Step -1
If Sheets("Sheet1").Cells(sr1, 4).Value <> "Il reste 0$ en crédit sur cette carte" Then
Sheets("Sheet1").Rows(4).EntireRow.Delete
Exit For
End If
Next sr1
End Sub


It gives me an error of type 9, maybe do I have to code VBA in french too? I can't figure further what I have to fix to make it work. I explain in my own words what I want the script to do:


If cell "O" does not equal "Il reste 0$ en crédit sur cette carte" then
delete this entire row
and I want the code to look to a range of "O4" to "O200"


AND preferably, before this I would also like to automatically sort the cells A4 to O200 in an ascending manner calculated from column "E" as numbers, so the ultimate solution for me would be the following:


Sort cells A4 to O200 in ascending manner, calculated from column E as numbers, then
If cell "O" does not equal "Il reste 0$ en crédit sur cette carte" then
delete this entire row
and I want the code to look to a range of "O4" to "O200"



Any help would be really appreciated
Any idea? Please help me!



Mike, alias NuganX