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

Topic

New Reply

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

Reply | Quote

Replies

Hi

See attached file.

I changed..
If ActiveCell.Value = ActiveCell.Value = “0.000” Then
..to..
If ActiveCell.Value = 0 Then

zeddy

Reply | Quote

Hi

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

zeddy

Reply | Quote

..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

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

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

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

zeddy

Reply | Quote

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

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

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.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks RG,
Interesting coding, I’ll check it out and see how it goes.

Reply | Quote