Finding zero value in a column and deleting entire row 1

[sunnytahir]

Dear Sir,

I have some data in "column D". I values are from the
result of a formula. I want to delete entire rows which
contain zero values.

I shall be very thankful for any help

Best regards.
Tahir

 

[TonyJollans]

Hi Tahir,

Autofilter on Column D, value zero
Delete all rows

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[KenWright]

After autofiltering on 0, may need to select the data and then use Edit / Go To / Visible cells only before deleting the rows.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[sunnytahir]

Dear TonyJollans & KenWright,

Thank you for your help. But my problem is that i do not
know the actual selection. i use this procedure:

first i select the cell "D5"
the following loop apply

Do While Not ActiveCell.Value = Empty
If ActiveCell.Value = 0 Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop

loop goes down but when 0 value accur it stops. i think
it consider 0 value to empty.

May you help this.

Thanks in advance.
Tahir

 

[TonyJollans]

Hi Tahir,

Apart from the invalid check for empty, that logic will delete all rows with a zero in column D - except for rows 1 to 4 - so I don't understand what you are saying.

Do you want a code solution? If so, although not very efficient, yours will do it - just change the first line to:
[blue][tt] Do While Not IsEmpty(ActiveCell)[/tt][/blue]

If you want more efficient, or more general, code please post back. If I have misunderstood, please post back with more details.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[sunnytahir]

Dear TonyJollans,

A Lot of Thanks for your help. It great helped me.
In my code 0 value cell was shown as empty cell and
do while loop stops.

May i ask why it consider 0 value cell as empty one.

Thanks again for your kind help.

Tahir

 

[KenWright]

Even if you want to stick with code you should still consider using Autofilter within your code. Looping through large ranges can be very time consuming, and using Autofilter within your code can speed it up considerably, eg:-

Sub DelZero()

Dim LastRow As Long
Dim rng1 As Range
Dim rng2 As Range
Application.ScreenUpdating = False

    LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
    Set rng1 = ActiveSheet.Columns("C:C")
    Set rng2 = ActiveSheet.Range(Cells(2, "C"), Cells(LastRow, "C"))

    On Error Resume Next
    With rng1
        .AutoFilter Field:=1, Criteria1:=0, Operator:=xlAnd
         rng2.SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With
Application.ScreenUpdating = True

End Sub

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[TonyJollans]

Hi Tahir,

An empty cell contains nothing.
When specifically checked for being Empty, it is empty!
When the cell is being considered as a string, it is treated as a zero length string: ""
When the cell is being considered as a number, it is treated as zero.

So when you say, zero was considered empty you really have it the wrong way round - it is an empty cell which is being considered as zero (you may have it set to display that way).

Although this can occasionally cause problems or confusion, there are many more cases where doing it differently would cause problems.

Excel is not really designed to work with lots of empty cells. It deals with them - and generally pretty well - so you just need to understand what it does.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[