VBA macro to remove rows if column has value?

[gd6noob]

Hello,

I want to know if theres a marco that can do this and if so, please share.

I want rows that contain any value other then 0 to be deleted.

Thanks in advanced.

 

[Fluff]

How about

Sub DelNon0()

    With Range("I2:I" & Range("H" & Rows.Count).End(xlUp).Row)
        .Value = Evaluate("if(" & .Offset(, -3).Address & "<>0,1,"""")")
        .SpecialCells(xlConstants).EntireRow.Delete
    End With

End Sub

 

[gd6noob]

Thanks... that worked..

another question.
What if the cell is blank instead of having the 0?
Tried to use your macro but so confused..

Thanks in advanced

 

[Fluff]

Try

Sub DelBlanks()

    Range("S2:S" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).EntireRow.Delete

End Sub

 

[gd6noob]

Im getting an error

Run-Time error '1004':
No Cells were found

 

[Fluff]

OK, a couple of questions
1) are the blank cells actually blank, or do they contain formulae returning ""
2) Will column A always have a value in the last row? If not what column will?

 

[gd6noob]

Hi Fluffy

Thanks for the quick reply.

As far as I know, the cells are empty..
I'm copying these from another program that I exported to excel.

So basically, I want to delete the rows that have a value under the Vacation column and leave the empty cells.


Thanks

 

[Fluff]

OK, I misunderstood.
Try this, but do it on a copy first as I don't think the blanks are blank

    With Range("S2:S" & Range("A" & Rows.Count).End(xlUp).Row)
        .Value = .Value
        .SpecialCells(xlConstants).EntireRow.Delete
    End With

 

[gd6noob]

Thanks Fluff..

Worked like a charm.

 

[Fluff]

Glad to help & thanks for the feedback