Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    Do Until If Cell range is 0.00 then Delete Row, won't Delete !

    Office Version 2003 ( till I get my 2010 sorted).

    The Loop works, no errors, it just will not DeleteRow with that particular 0.000 Value.
    No matter what I change the Value to, 0.0 or 0. it just won't Delete
    Everywhere else this method of deleting rows I don't need works fine.
    Even a Find and or Replace does not seem to pick it up.
    ??

    Thanks


    The code is,
    Code:
    Sub DELETE_ROWS()
    
    Sheets("DELETEROW").Select
    Sheets("DELETEROW").Range("H4").Select
    
    Selection.Offset(3, 0).Select    '*Down
    Do Until ActiveCell.Value = ""
    If ActiveCell.Value = ActiveCell.Value = "0.000" Then
    ActiveCell.EntireRow.Delete
    ActiveCell.Offset(-1, 0).Select
    End If
    ActiveCell.Offset(1, 0).Select
    Loop
    Sheets("DELETEROW").Range("A2").Select
    
    ActiveWorkbook.Save
    
    End Sub
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi

    See attached file.

    I changed..
    If ActiveCell.Value = ActiveCell.Value = "0.000" Then
    ..to..
    If ActiveCell.Value = 0 Then

    zeddy
    Attached Files Attached Files

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi

    ..But just for info, it is always better to delete rows from 'the bottom up'.

    zeddy

  4. #4
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    ..But just for info, it is always better to delete rows from 'the bottom up'.

    zeddy
    Thanks zeddy,
    Yes I know there is a correct method/s, a work in progress, I got one of those in a older project not
    written by me, but each time I try to work out how to change it's references, for example this section
    here;
    Code:
    LastRow = Worksheets("Sheet1").Range("B65536").End(xlUp).Row
        For i = LastRow To 2 Step -1
            If Worksheets("Sheet1").Cells(i, 7).Value = "" Then
                Worksheets("Sheet1").Cells(i, 7).EntireRow.Delete
    it goes "AWOL"....

    The complete code I have stored, ( and one of my favorite snippets of code ) is;
    Code:
    Sub DeleteRows()
    
        Sheets("Sheet1").Select
        Sheets("Sheet1").Range("A1").Select
        Dim LastRow As Integer
        Dim i As Integer
        LastRow = Worksheets("Sheet1").Range("B65536").End(xlUp).Row
        For i = LastRow To 2 Step -1
            If Worksheets("Sheet1").Cells(i, 7).Value = "" Then
                Worksheets("Sheet1").Cells(i, 7).EntireRow.Delete
            End If
        Next i
        LastRow = Worksheets("Sheet1").Range("B65536").End(xlUp).Row
    
        For i = 1 To LastRow
            Worksheets("Sheet1").Cells(i, 1).Value = i - 1
             
     Next i
    
    End Sub
    I did try to use it, it may need explaining and some tweaking. It does show as suggested,
    deletes Rows correctly.

    XP

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    XP,

    How about this to simplify and to work on all versions of Excel.
    Code:
    Sub DeleteRows()
    
        Dim lLastRow As Long  'For versions >=2007 you need a long!
        Dim lCntr    As Long
    
        Sheets("Sheet1").Select
        
        lLastRow = Cells(Rows.Count, 2).End(xlUp).Row   '2 is Col B, etc.
    
        For lCntr = lLastRow To 2 Step -1
            If Cells(lCntr, 7).Value = "" Then
                Cells(lCntr, 7).EntireRow.Delete
            End If
        Next lCntr
    
        lLastRow = Cells(Rows.Count, 2).End(xlUp).Row
    
        For lCntr = 1 To lLastRow
           Cells(lCntr, 1).Value = lCntr - 1
        Next lCntr
    
    End Sub     'DeleteRows()
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi RG

    ..and for those wondering..
    If Cells(lCntr, 7).Value = "" Then

    ..the 7 refers to columnn [G]
    (A=1, B=2, ..etc)

    zeddy

  7. #7
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    zeddy, saw the obvious error, missed it.

    Code:
    If ActiveCell.Value = ActiveCell.Value = "0.000" Then ':o
    
    If ActiveCell.Value = "0" Then
    RG
    tried codes as suggested to, and the original... it goes "AWOL".

    Note though, the top 3 rows ( 1,2 and 3), if blank must remain as they are, not to be deleted.
    The Workbook has all examples shown so far.

    Thanks
    Attached Files Attached Files

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    XP,

    The code works fine it's the test data that is faulty!
    There is no data in Col B which is the column we are using to set the Last Row value.
    If you put something in Col B for each row it will then delete the rows with a blank value in Col H.
    If you want it to also delete zero values the code needs this.
    Code:
        For lCntr = lLastRow To 4 Step -1
            If Cells(lCntr, 8).Value = "" Or _
               Cells(lCntr, 8).Value = 0 Then
                Cells(lCntr, 8).EntireRow.Delete
            End If
        Next lCntr
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Thanks RG,
    I'll test it after a few runs,
    that is why I supply a convenient workbook.
    To show how I have to have it in other processes.
    Not always column B, it's series of processes and calculations.
    Column B was a previous process where it still works OK.

    It's when I try to tweak it to do other columns for other calculations,
    it faults.

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    XP,

    If you need to call it from different places with different columns you can do this:
    Code:
    Sub TestCall()
    
        DeleteRows "TestDelete", 3, 10
        
    End Sub
    
    Sub DeleteRows(zShtName As String, lCntCol As Long, lTestCol As Long)
    
        Dim lLastRow  As Long  'For versions >=2007 you need a long!
        Dim lCntr     As Long
        Dim wksCaller As Worksheet  'Save calling Sheet
    
        Set wksCaller = ActiveSheet
        Sheets(zShtName).Select
        
        lLastRow = Cells(Rows.Count, lCntCol).End(xlUp).Row   '2 is Col B, etc.
    
        For lCntr = lLastRow To 4 Step -1
            If Cells(lCntr, lTestCol).Value = "" Or _
               Cells(lCntr, lTestCol).Value = 0 Then
                Rows(lCntr).EntireRow.Delete
            End If
        Next lCntr
    
    '*** Don't know what this code block does but you may not want it here ***
        lLastRow = Cells(Rows.Count, 2).End(xlUp).Row
    
        For lCntr = 1 To lLastRow
           Cells(lCntr, 1).Value = lCntr - 1
        Next lCntr
    '*** End questionable code if generalizing sheets.
    
        wksCaller.Activate   'Return to calling sheet!
        
    End Sub     'DeleteRows()
    Just change the Macro for the Delete Row-RG button to TestCall. You'll of course need a calling sub for each different sheet!

    I added the sheet TestDelete to the attached file for testing purposes.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #11
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Thanks RG,
    Interesting coding, I'll check it out and see how it goes.

Posting Permissions

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