Delete row based on single column value

**Urchino** · 11-17-2011, 03:01 AM

Hello,

I notice there is another thread on this, not sure if i should combine it as it stated [solved].

My script is running perfectly, but when i add this code in the script,

 'Delete rows that have nothing balances on Column W
    Dim Rng As Range, ix As Long
    Set Rng = Intersect(Range("W:W"), ActiveSheet.UsedRange)
    For ix = Rng.Count To 1 Step -1
    If Rng.Item(ix) = "" Then
    Rng.Item(ix).EntireRow.Delete
    End If
Next

    Range("Q13").Select

the script continues to run without stopping. When i choose to debug, it will highlight my End If.

Could someone advise me what is wrong with my code?

Thanks in advance.

Cheers

**Paul** · 11-17-2011, 03:21 AM

Hi Urchino, welcome to the forum.

I'd recommend a small adjustment:

'Delete rows that have nothing balances on Column W
    Dim LR as Long, i as Long
    LR = Range("W" & Rows.Count).End(xlUp).Row

    For i = LR To 1 Step -1
        If Cells(i, "W").Value = "" Then Cells(i, "W").EntireRow.Delete
    Next i

Also note that if this is part of a change event -- or if you have a Worksheet_Change event that does something else -- you should disable events within this macro so you are not triggering that event repeatedly. You can do so by adding this before the deletions:
[CODE]Application.EnableEvents = False[CODE]
and this after:

Application.EnableEvents = True

One final note: If your macro crashes before re-enabling events, no other macros will work in Excel. You will need to re-enable macros by running that last line of code by itself (you can put it in a small Sub/EndSub).

A better way would be to add error handling to your macro, so that if an error does occur it is trapped and you can re-enable events and other settings before the macro completely finishes running. For that, do some searching here (many examples) and Google.

**Urchino** · 11-17-2011, 09:24 PM

Thanks Paul.

It still can't stop running after i amend. I split it out Sub End Sub and it works. After which i combine it again and it works with the code you provided. Thank you once again.

Cheers