If Cell value is <> 4 then delete Entire Row

[NessPJ]

Hi all,

I am having trouble getting the following cleanup code to work:

    Dim i As Integer

    For i = 2 To Range("A" & "65536").End(xlUp).Row Step 1       
        LResult = Len(Range("B" & i).Value)
        If LResult < 4 Or LResult > 4 Then
        Range("B" & i).EntireRow.Delete
        End If
    Next i

Column B has several values ranging from "0" to "999999999" and "AAAAAAAAA", but i want all rows deleted unless Cell B of that specific row contains a value between "1000" and "9999".

Am i doing something wrong?

 

[NessPJ]

Ugh, i'm not allowed to edit my post a few mins after posting.

This line is also in the code:

Dim LResult As String

 

[Fluff]

Try changing your dim statement to

Dim LResult As long

As you are testing the length of the value it will return a number

 

[NessPJ]

That still doesn't seem to cut it so it seems.

    Dim LResult As Long
    Dim i As Integer


    i = 0
    
    For i = 2 To Range("A" & "65536").End(xlUp).Row Step 1
         LResult = Len(Range("B" & i).Value)
        If LResult < 4 Or LResult > 4 Then
        Range("B" & i).EntireRow.Delete
        End If
    Next i

 

[Fluff]

When deleting (or inserting) rows it's always best to work bottom up. So try

 For i = Range("A" & "65536").End(xlUp).Row To 2 Step -1

If this doesn't resolve the problem, then please explain what is not working.

 

[NessPJ]

When deleting (or inserting) rows it's always best to work bottom up. So try

 For i = Range("A" & "65536").End(xlUp).Row To 2 Step -1

If this doesn't resolve the problem, then please explain what is not working.

Thanks a lot, its working now! How odd this wont work with a normal search order through the range. Any ideas why perhaps?

 

[Fluff]

If you delete row 2, then row 3 moves up to become row 2 & the macro then checks row 3 (which was originally row 4).
So in this scenario the original row 3 will never get checked.

 

[NessPJ]

That makes total sense.. thanks!

 

[Fluff]

Glad to help & thanks for the feedback

 

[NessPJ]

Okay, so i got around to actually testing this in practise.
Both tables have about 1200 entries and the code seems to take ages to execute (like...30 minutes).

Is there any way we would be able to speed this up?

[Edit]

Okay, so i added the following lines to the start and the end of the routine:

 Application.Calculation = xlManual

 Application.Calculation = xlAutomatic

This made all the difference in the world...the routine now finishes in less than 20 seconds.