delete row if the value in column J contains 0

[GirishDhruva]

Hi Everyone,
Here i am trying to delete entire row if the value in column 'I' is 0.
The below code works but it also deletes the empty spaces that are present in column'I' where that shouldn't happen
Can anyone suggest/guide me what changes i should make ??????

    last = Cells(Rows.Count, "D").End(xlUp).Row 
    For i = last To 4 Step -1
        If Cells(i, "I").Value = 0 Then
                Cells(i, "I").EntireRow.Delete
        End If
   Next i

like
it should delete only the the value with 0(entire row), not the cell that contains empty spaces

column D	Column I
1	1234
2	0
3
4	54213
5	0
6	0
7	3456
8
9	0

<tbody>
</tbody>

Thanks in advance

 

[Joe4]

Try this:

    last = Cells(Rows.Count, "D").End(xlUp).Row 
    For i = last To 4 Step -1
        If (Cells(i, "I").Value = 0) And (Len(Cells(i, "I") > 0) Then
            Rows(i).Delete
        End If
   Next i

 

[GirishDhruva]

@Joe4 thanks for your effort but this also doesn't work as i needed.
My main requirement is, the values in column 'I' which contains 0 should be deleted and the empty cells in column'I' shouldn't be deleted

Thanks in advance

 

[Joe4]

Sorry, I was missing a parentheses in my code. It should be:

    last = Cells(Rows.Count, "D").End(xlUp).Row
    For i = last To 4 Step -1
        If (Cells(i, "I").Value = 0) And (Len(Cells(i, "I")) > 0) Then
            Rows(i).Delete
        End If
   Next i

 

[GirishDhruva]

Sorry, I was missing a parentheses in my code. It should be:

    last = Cells(Rows.Count, "D").End(xlUp).Row
    For i = last To 4 Step -1
        If (Cells(i, "I").Value = 0) And (Len(Cells(i, "I")) > 0) Then
            Rows(i).Delete
        End If
   Next i

@Joe4 your solution also deletes blank spaces in column'I' where that shouldn't happen

It should only delete the rows which contains 0 in column'I'

 

[Joe4]

That tells me that your cells are not really empty, that there is something (like spaces in there).
I was kind of confused when you said "empty spaces". A cell can either be empty, or have spaces. If it has spaces, it is not empty.
I was assuming that the cells were empty, but it sounds like you have spaces in them.

If that is the case, and they are just normal spaces, then the following update should work:

    last = Cells(Rows.Count, "D").End(xlUp).Row
    For i = last To 4 Step -1
        If (Cells(i, "I").Value = 0) And (Len(Trim(Cells(i, "I"))) > 0) Then
            Rows(i).Delete
        End If
   Next i

If that does NOT work, then you do not have normal spaces in there, and we will need to identify exactly what is in those cells to see what we are dealing with.

Find one of these cells, and enter these formulas in some blank cells and let me know what they return.
Let's say that the cell is I4, then use these formulas:
=LEN(I4)
=CODE(LEFT(I4,1))
and tell me what they return.

 

[GirishDhruva]

For =LEN(I4) o/p is 0 but
For =CODE(LEFT(I4,1)) its throwing me an #Value ! error

 

[Trebor76]

Hi GirishDhruva,

Not much different but this works for me:

Option Explicit
Sub Macro1()
    
    Dim last As Long
    Dim i As Long
    
    Application.ScreenUpdating = False
    
    last = Cells(Rows.Count, "I").End(xlUp).Row
    
    For i = last To 1 Step -1
        If Val(Range("I" & i)) = 0 Then
            Rows(i).Delete
        End If
   Next i
   
   Application.ScreenUpdating = True
   
End Sub

Regards,

Robert

 

[GirishDhruva]

Thanks for your effort @Trebor76 but your code is also deleting blank/empty cells which are present in column'I', but that shouldn't happen only the cells that contains zero should be deleted

I tried with the below data

Column D	Column I
1	1
2	2
3	0
4	4
5	0
6
7	0
8	8
9

<tbody>
</tbody>

Hi GirishDhruva,

Not much different but this works for me:

Option Explicit
Sub Macro1()
    
    Dim last As Long
    Dim i As Long
    
    Application.ScreenUpdating = False
    
    last = Cells(Rows.Count, "I").End(xlUp).Row
    
    For i = last To 1 Step -1
        If Val(Range("I" & i)) = 0 Then
            Rows(i).Delete
        End If
   Next i
   
   Application.ScreenUpdating = True
   
End Sub

Regards,

Robert

 

[Trebor76]

Not sure as it worked for me

Can you use a hosting site like www.box.com and provide a link to your workbook so we can see what's happening?

Thanks,

Robert