Macro to delete row if cell <> value...

[TShelt]

Hey guys,

I need a short and simple macro that deletes any row that contains "*EDS*" in column A...this is the code I am using but it doesnt seem to be working correctly, it hangs up on line 2...

Please Login or Register  to view this content.

[jasondu]

Do the cells in column A only contain either *EDS* or no *EDS*? or are you trying to search through a paragraph of text for each cell in column A, and only delete if within that paragraph of text *EDS* shows up?

Jason

[TShelt]

No, the cells will have various information....EDS will just be in the middle of a lot of text.

Example:

[excelaron]

FinalRow = Cells(Rows.Count, 56).End(xlUp).Row
For i = FinalRow To 1 Step -1

If Cells(i, 1).Value = "*EDS* Then
Cells(i,1).Delete
End If

Next i

[jasondu]

Hi,

try changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

OR replace entirely with this code ..

Please Login or Register  to view this content.

[TShelt]

Jason, that does work - however I had to run it 3 times in order for it to delete all the EDS rows...
For some reason it is not catching them all in one run...i am going to try it a couple more times

[jasondu]

hmm it shoudlve deleted all (it worked for me), but anyways see modified post I fixed your code for a even simpler solution

[TShelt]

I managed to work around it and add it 3 ways...not the best way to do it, but it works lol -

Also one more question, what is the code change for application.match if say I didnt want it to match "*EDS*". If that makes sense...

So logic would be: If it does not equal "*EDS*" then delete entire row.

[TShelt]

I appreciate all your help with this

[jasondu]

Hey Tshelt,

Did you try changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Also .. do you have any blanks in column A? how many rows do you have? I tried my code and it worked for up to 300 lines with varying *EDS* in random text can you upload your sheet?

[TShelt]

I cannot upload my sheet as it has info on it I dont want out...

The sheet has 365 rows but it varies weekly and no blanks.