VBA to delete rows when values meet certain criteria?

[thommo41]

Hi,

I have a list of data that contains some rows that I dont want. These rows aren't necessarily in order.
Basically, where the value in column A of that row is LESS THAN 3 characters long, I want to delete the row.
I have done the following routine that does this, but as it deletes a row, it doesn't check the immediate row below, as the loop doesn't take into account the deleted row (does that make sense?)

Sub delrow()
alan = Sheets("Sheet1").UsedRange.Rows.Count
For Row = 2 To alan
    If Len(Range("A" & Row).Value) < 3 Then
    Rows(Row).Delete Shift:=xlUp
    End If
Next
End Sub

I either need a simple fix to make this simple loop take into account when it deletes a row, the next row is infact the one it is looped on, or I need a better way of deleting all rows in a range where the Cell range("A" & row#) is less than 3 characters.

A solution for the loop taking into account the deleted row(s) would be nice anyway, and if some better code is mentioned also, that would be a bonus :wink:

Thanks in advance
Alan

 

[Domski]

Hi,

You should run the delete code from the bottom up like this:

Sub delrow() 
alan = Sheets("Sheet1").UsedRange.Rows.Count 
For Row = alan To 2 Step -1
    If Len(Range("A" & Row).Value) < 3 Then 
    Rows(Row).Delete Shift:=xlUp 
    End If 
Next 
End Sub

Hope it helps.

Dom

 

[big_mac]

You might want to check out this or even search it for info, Chip is the Man!
http://www.cpearson.com/excel/deleting.htm