Marco not counting rows correctly

[BBATESJR15]

Dim c As Range
Dim Remove As Integer

Remove = Sheets("TEST").Range("A" & Rows.Count).End(xlUp).Row


For Each c In Sheets("TEST").Range("A1:A" & Remove)


If Len(c) <= 8 Then
c.EntireRow.Delete
End If
Next c

Hi,

I'm using the code above to delete rows that have 8 characters or less. The code is working, but I have to run the macro a least 5-6 time before it removes all of the rows that have 8 characters or less. I have used this line of code a few times and I've never had this problem. It seems to a counting issue because it's skipping rows as it looks for the info. Any help would be greatly appreciated.

Thanks,

B

 

[Fluff]

When deleting/inserting rows you should work from the bottom up.
The problem is that is you delete row 2, row 3 becomes row 2 & then you go to next c, which will be row 3 (the original row 4). So the original row 3 never gets checked. try

Dim c As Range
Dim Remove As Integer

Remove = Sheets("TEST").Range("A" & Rows.Count).End(xlUp).Row


For c = Remove To 1 Step -1
   If Len(Range("A" & c)) <= 8 Then
      Rows(c).EntireRow.Delete
   End If
Next c

 

[BBATESJR15]

I getting a Type mismatch error for c with this code.

 

[Fluff]

Apologies, missed that. It should be

Dim c As Long

 

[BBATESJR15]

That was the problem. Thanks for all of the help!!

 

[Fluff]

Glad to help & thanks for the feedback