+ Reply to Thread
Results 1 to 17 of 17

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

  1. 01-18-2012, 09:21 AM #1
    Nikeyg
    Nikeyg is offline
    Forum Contributor
    Join Date
    08-04-2010
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    110

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

    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?
    Last edited by Nikeyg; 01-19-2012 at 08:18 AM.
    Reply With QuoteRegister To Reply
  2. 01-18-2012, 09:24 AM #2
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    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.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
  3. 01-18-2012, 09:36 AM #3
    Nikeyg
    Nikeyg is offline
    Forum Contributor
    Join Date
    08-04-2010
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    110

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

    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
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 01-18-2012, 10:05 AM #4
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    So should entries like ---------- be removed? Also, should the entire row be deleted irrespective if there is data in columns B to D?
    Reply With QuoteRegister To Reply
  5. 01-18-2012, 10:09 AM #5
    Nikeyg
    Nikeyg is offline
    Forum Contributor
    Join Date
    08-04-2010
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    110

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

    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.
    Reply With QuoteRegister To Reply
  6. 01-18-2012, 10:30 AM #6
    nkf531
    nkf531 is offline
    Registered User
    Join Date
    12-27-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    43

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

    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?
    Reply With QuoteRegister To Reply
  7. 01-18-2012, 10:34 AM #7
    Nikeyg
    Nikeyg is offline
    Forum Contributor
    Join Date
    08-04-2010
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    110

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

    Unfortunately that didn't delete anything nkf531.
    Not sure why.
    Reply With QuoteRegister To Reply
  8. 01-18-2012, 11:02 AM #8
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    Use this code - 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 01-18-2012, 11:09 AM #9
    Nikeyg
    Nikeyg is offline
    Forum Contributor
    Join Date
    08-04-2010
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    110

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

    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.
    Reply With QuoteRegister To Reply
  10. 01-18-2012, 11:23 AM #10
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    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.
    Reply With QuoteRegister To Reply
  11. 01-18-2012, 12:36 PM #11
    Nikeyg
    Nikeyg is offline
    Forum Contributor
    Join Date
    08-04-2010
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    110

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

    Yeah, have tried that. Nothing has changed. All GBP lines and zero values still remain.
    Reply With QuoteRegister To Reply
  12. 01-18-2012, 01:27 PM #12
    elusivedonnam
    elusivedonnam is offline
    Registered User
    Join Date
    01-18-2012
    Location
    Canning, Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

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

    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?
    Reply With QuoteRegister To Reply
  13. 01-18-2012, 01:27 PM #13
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    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.
    Reply With QuoteRegister To Reply
  14. 01-18-2012, 01:28 PM #14
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    @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.
    Reply With QuoteRegister To Reply
  15. 01-19-2012, 06:03 AM #15
    Nikeyg
    Nikeyg is offline
    Forum Contributor
    Join Date
    08-04-2010
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    110

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

    Have attahced it. I've tried my best to keep basic data for sheet integrity. Have tried running the macro again, just wont work
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  16. 01-19-2012, 07:39 AM #16
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    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.
    Reply With QuoteRegister To Reply
  17. 01-19-2012, 08:18 AM #17
    Nikeyg
    Nikeyg is offline
    Forum Contributor
    Join Date
    08-04-2010
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    110

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

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

    Thanks for the help. Sorry to have wasted your time. Kudos has been added.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1