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?

 

[JazzSP8]

Application.ScreenUpdating = False

And

Application.ScreenUpdating = True

At the start and end of the code will also help speed things up, use them at the same time you switch Calculation on and off

 

[NessPJ]

Application.ScreenUpdating = False

And

Application.ScreenUpdating = True

At the start and end of the code will also help speed things up, use them at the same time you switch Calculation on and off

Yeah, i was already familiar with that one, but thanks!

 

[NessPJ]

Hi,

One more question; i have another table with a similar situation.

I would like to fill Column A with a "1" if the value in Column C only exists 1 time inside the range determined by Column B (Day numbers 1 through 7).
If the value in Column C exists twice within a single day (eg. Day 1) i would like the value in Column A for the second hit to be "2".

I have an example of the table i use here:
https://www.dropbox.com/s/d5x5l9i7ouoehoz/Book1.xlsx?dl=0

If anyone could help me out with this one it would be much appreciated!

 

[Fluff]

How about in A2
=COUNTIFS(B$2:B2,B2,C$2:C2,C2)

 

[NessPJ]

How about in A2
=COUNTIFS(B$2:B2,B2,C$2:C2,C2)

Thats works! But when i try to use it in my routine, i get a Runtime Error: 1004 Application-defined or Object-defined error.
Any idea how to fix that?

LastRowATImpSht = Range("C" & "65536").End(xlUp).Row

    Range("A2").Formula = "=COUNTIFS(B$2:B2;B2;C$2:C2;C2)"
    Range("A2").Copy Range("A2:A" & LastRowATImpSht)

 

[Fluff]

If you enter the formula manually in the sheet do you use , or ; as the separator?

 

[NessPJ]

I use a ; as a seperator.

[Edit]

I just noticed that in VBA it still has to be a , (comma) even if i use a ; (semicolon) in Excel!

Thanks for the help!

 

[Fluff]

You're welcome