Macros: insert value and remove rows

[hello9000]

Hi,
I have two different codes that I am working on, and need som help figuring out.

#1: I have this Macro-code, it is intended to add the value 1, to column S if the corresponding column Q have value that is <= 1000 and >= 9999 (equal to or between 1000-9999).

So far this code only add the value 1 to the S column if the corresponding Q column is exactly 1000 or 9999, how can this code be modified to also inched the values between 1000-9999?

With Range("s4:s" & LastRow)
        t = .Offset(, 1).Address
        .Offset(, -2).Value = Evaluate("=if(len(" & t &  "),LEFT(" & t & ",SEARCH("" ""," & t & "&""  "")-1)+0," & """"")")
        s = .Address
        q = .Offset(, -2).Address
        .FormulaR1C1 = "=if(or(rc[-2]<=1000,rc[-2]>=9999),1,0)"
        .Value2 = .Value2
    End With

#2: I have this Macro-code that is intended to remove all rows that does not contain the word "ARG1" and "ARG2" in column A.

Sub Clear_test()

Set r = Range(Range("A4"), Range("A65535").End(xlDown))
For Each c In r

If Trim(c) <> "ARG1" And Trim(c) <> "ARG2" Then c.EntireRow.Delete
Next c

End Sub

So far this code seams to run in an internal loop, that never ends.. but it does remove the unwanted rows, just does not stop running!
And the numbers of rows in my sheet is not a fixed value. For now it contains almost 5000 rows but I think it will grow until 16000 at the end of this year!

Please help!

 

[ColinKJ]

Hi hello9000,

In the first code you could try changing:

.FormulaR1C1 = "=if(or(rc[-2]<=1000,rc[-2]>=9999),1,0)"

To:

.FormulaR1C1 = "=if(or(rc[-2]>=1000,rc[-2]<=9999),1,0)"

And for the second code you may want to try:

Sub Clear_test()
 
Set r = Range(Range("A4"), Range("A65535").End(xlDown))
For  a= r to 4 step-1
 
If Cells(a, 1) <> "ARG1" And Cells(a, 1) <> "ARG2" Then Rows(a & ":" & a).EntireRow.Delete
Next c
 
End Sub

 

[hello9000]

Yes, the first sugestion did the job! Thank you About the second code you gave me, I keep getting "Invalide Next control variable reference"-Error, have tried changing Next c to Next a, then I get an error in the: "For a = r To 4 Step -1" line.

 

[ColinKJ]

Hi

Sorry I should have tested it, try this code.

R = Range("A" & Rows.Count).End(xlUp).Row
For a = R To 4 Step -1
If Cells(a, 1) <> "ARG1" And Cells(a, 1) <> "ARG2" Then Rows(a & ":" & a).EntireRow.Delete
Next a

 

[hello9000]

It is very strange, but after this code has ran for abount 3min, all the cells from row 4 and down is deleted, in every column!!

 

[ColinKJ]

H1 hello9000,

If I send you a PM with my email add, are you able to send me an example of the data, so I can see what is happening.

 

[hello9000]

Sure! Thank you