Go Back   Tech Support Forum > The IT Pro > Programming

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

This is a discussion on How to delete an entire row in Excel 2007 if value of a cell isnt equal to some text? within the Programming forums, part of the Tech Support Forum category.


Closed Thread
 
Thread Tools Search this Thread
Old 06-08-2009, 08:39 AM   #1
Registered Member
 
Join Date: Jun 2009
Posts: 1
OS: vista sp2



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

__________________
nuganx is offline  
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is on
Smilies are on
[IMG] code is on
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Post a Question


» Site Navigation
 > FAQ
  > 10.0.0.2


All times are GMT -7. The time now is 06:27 PM.


Copyright 2001 - 2014, Tech Support Forum

Windows 7 - Windows XP - Windows Vista - Trojan Removal - Spyware Removal - Virus Removal - Networking - Security - Top Web Hosts