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?)
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
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?)
Code:
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