Excel 2007 : Macro to delete rows based on cell values equalling zero and text equalling GBP

[Nikeyg]

Hi guys.

I have a sheet with data in columns A through to E.
In column E are the values of funds in accounts. Not all cells have values though, some have text or symbols and these are interspersed amongst the data.
I need my macro to delete all lines that do not equal zero.

I've tried using this code, but it doesn't delete anything. I'm guessing this is because of the cells which are not value cells

Please Login or Register  to view this content.

I've also tried this code, this deletes most of the 0 value rows, but for some reason, doesn't delete the bottom 2.

Please Login or Register  to view this content.

If I put that code in the macro twice more, then it delete the final 2 lines. So I know it isn't that values of the cells themselves causing this problem.

In a slight change to the code, I'm trying to use this code to delete rows where the data in column A reads GBP

Please Login or Register  to view this content.

However, it doesn't appear to delete anything.

Any ideas why I just can't get these pieces of code to work?

[arlu1201]

Can you give some samples of what kind of data you are looking at to delete? If i ask the code to delete anything that is not equal to 0, it will delete amounts as well such as 3500, 2440 etc.

[Nikeyg]

I want the macro to remove rows where the value in column E is zero and then to remove rows where the data in column A is "GBP"

I've attached an example. Normally columns B, C, and D would have data, but this is sensitive info and I had to remove it

[arlu1201]

So should entries like ---------- be removed? Also, should the entire row be deleted irrespective if there is data in columns B to D?

[Nikeyg]

It's only the rows with the GBPs in column A and zero values in column E that I want removed. The rest of the data can stay as far as I'm concerned. I just can't seem to get it to go away.

[nkf531]

Sub DeleteZero()

Dim intEnd As Integer

intEnd = 100

Range("e1").Select

Do Until ActiveCell.Row = intEnd

On Error Resume Next
If Int(ActiveCell.Value) = "0" Then

Range(ActiveCell.Row & ":" & ActiveCell.Row).Delete
intEnd = intEnd - 1
Else
ActiveCell.Offset(1, 0).Select
End If

Loop

End Sub

does this work?

[Nikeyg]

Unfortunately that didn't delete anything nkf531.
Not sure why.

[arlu1201]

Use this code -

Please Login or Register  to view this content.

[Nikeyg]

Unfortunately that hasn't worked either. All the GBP entries and the 0 value lines remain intact.
I can't understand why it just isn't working.

[arlu1201]

Have you copied the code in a blank module - Press Alt + F11, you will get a code window. On the left hand side, you will see Microsoft excel objects. Right click on it and select Insert -> Module. Copy the code over there. Go back to your main page, select View -> Macros and select the macro delete_char.

It should work.

[Nikeyg]

Yeah, have tried that. Nothing has changed. All GBP lines and zero values still remain.

[elusivedonnam]

I am not sure if my problems is the same or not. I have a spread sheet that has a lot of blank rows in it which I want to delete. Would any of these solutions fix that?

[arlu1201]

Can you attach the file you are using the macro with? There could be one small issue. I ran it at my end and it works fine.

[arlu1201]

@elusivedonnam - Welcome to the Forum.

Please post your question in a new thread. Its against the forum rules to post your thread in the thread of another.

[Nikeyg]

Have attahced it. I've tried my best to keep basic data for sheet integrity. Have tried running the macro again, just wont work

[arlu1201]

Your code states -

Please Login or Register  to view this content.

and your data is in the 2nd sheet. Just change the code to With Worksheets(2) or if you want use the sheet name (incase the order of the sheets wont be the same always) this way -

Please Login or Register  to view this content.

[Nikeyg]

Ahh. Had to be something simple didn't it?

Thanks for the help. Sorry to have wasted your time. Kudos has been added.