+ Reply to Thread
Results 1 to 4 of 4

vba code that deletes entire row if there is a zero in that row of my highlighted area

  1. #1
    Registered User
    Join Date
    04-17-2015
    Location
    Branchburg, NJ
    MS-Off Ver
    windows 7
    Posts
    9

    vba code that deletes entire row if there is a zero in that row of my highlighted area

    Basically the code that i am using now is not working. my macro is supposed to take the highlighted area and throw it into another worksheet and at the tail end of that, delete any rows in either that column, or in the highlighted area that contain zero.

    So before the macro...

    A B C
    1
    - 0
    - 0
    - 98
    - 0
    - 20
    - 0
    - 0
    - 15
    - 70
    2


    After the macro
    1
    - 98
    - 20
    - 15
    - 70
    2




    But this is what my macro is doing right now and i'm not sure why...

    1
    - 0
    - 98
    - 0
    - 20
    - 0
    - 15
    - 70
    2


    It's as if it only deletes the consecutive zeroes. It should be the right coding, but if anyone has an idea why this could be happening, please let me know.

    Also. I don't have an example file ready to be uploaded right now. I am just hoping for a quick fix. If no one can help me by the 27th, I will consider posting an example file, but that would take some time without compromising the information contained in the file.

    Thank you in advance to anyone that attempts to help. Honestly, I'm glad i can refer to this community.

    This is my code just below...

    ____________________________________________________________________________________________________________
    Sub submission()
    '
    ' submission Macro
    '

    '
    Dim ChkRange As Range
    Dim Cell As Range
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Catalog").Select
    Dim NextRow As Range
    Set NextRow = Range("A" & Sheets("Catalog").UsedRange.Rows.Count + 1)
    NextRow.PasteSpecial Paste:=xlValues, Transpose:=False
    Application.CutCopyMode = False
    Set NextRow = Nothing
    Set ChkRange = Range("B:B")
    For Each Cell In ChkRange
    If Cell = "0" Then
    Cell.EntireRow.Delete
    End If
    Next
    End Sub

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,428

    Re: vba code that deletes entire row if there is a zero in that row of my highlighted area

    Generally speaking, if you want to delete rows in a loop, you need a loop counter and count from the bottom up. Either that, or build a range of cells to be deleted and then delete them all in one go at the end of the loop.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    05-02-2015
    Location
    calgary alberta
    MS-Off Ver
    2012
    Posts
    205

    Re: vba code that deletes entire row if there is a zero in that row of my highlighted area

    Sorry not my poet and don't want to hijack. What's the reason for counting in reverse? I get building a dynamic range but why count down?

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,428

    Re: vba code that deletes entire row if there is a zero in that row of my highlighted area

    Your poet? Don't want to hijack? But you are going to anyway??

    Consider, if you want to process all the cells in the range A2:A10, and only A4 and A5 meet the criteria to be deleted. When you process cell A4, it meets the criteria and you delete it. Everything else now moves up one cell so cell A5 becomes cell A4 ... but the counter is now looking at cell A5, which was cell A6, and it doesn't meet the criteria for deletion. So, you only delete one of the two cells that should have been deleted.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Creating a line chart with area highlighted
    By mindfire in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-28-2014, 03:59 AM
  2. Code to set print area for entire page
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-20-2013, 10:45 AM
  3. [SOLVED] Modify code to only work on a selected area and not the entire sheet
    By Harribone in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-23-2013, 03:07 PM
  4. Double clicking protected cell deletes entire contents
    By oenleunc in forum Excel General
    Replies: 11
    Last Post: 09-30-2012, 02:45 PM
  5. Creating Excel Macro That Deletes Entire Row with Specific Text
    By Efar68 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-27-2011, 04:33 AM
  6. How to count the members of highlighted area?
    By wali in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-06-2008, 10:17 AM
  7. Useful code that deletes entire rows based on formatting
    By Ralfie in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-20-2007, 01:02 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1