Excel Macro; Insert value and remove rows

Discussion in 'Software' started by hello9000, May 18, 2011.

  1. hello9000

    hello9000 Private E-2

    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?

    Code:
    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.

    Code:
    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!
    Edit/Delete Message
     
  2. Wyatt_Earp

    Wyatt_Earp MajorGeek

    Code:
    if(or(rc[-2]<=1000, rc[-2]>=9999),1,0)
    I think your logic is wrong. This statement says if rc[-2] is less than or equal to 1000 or if it is greater than or equal to 9999. I think you want something like:

    Code:
    if(and(rc[-2]>=1000, rc[-2]<=9999),1,0)
    As for #2, I think the problem is that you are modifying the contents of your iterator while in the loop. Try saving a list of rows to delete, then after the loop, delete all the rows at the same time. I think that might fix your problem.
     
  3. hello9000

    hello9000 Private E-2

    Thank you :)

    I almost got my second code working now, and manage to remove all columns that contains one argument, but not two.

    This code is not working:

    Code:
    Sub delete()
    
    Range("F4:F5000").Select
    For Each cell In Selection
    notDelete1 = "Arg1"
    notDelete2 = "Arg2"
    If Not cell.Value = notDelete1 Or notDelete2 Then
    cell.ClearContents
    End If
    Next cell
    Range("F4:F5000").Select
    Selection.SpecialCells(xlCellTypeBlanks).EntireRow.delete
    End Sub
    But if I modify this part, It will remove rows based on only one of the arguments, not both.

    Code:
    notDelete1 = "Arg1"
    If Not cell.Value = notDelete1 Then
    cell.ClearContents
    I would like it to considerate both the arguments, bot just only Arg1.

    Please help!
     
  4. Wyatt_Earp

    Wyatt_Earp MajorGeek

    That's because in this code:

    Code:
    If Not cell.Value = notDelete1 Or notDelete2 Then
    The two expressions that you are evaluating are
    1) "Not cell.Value = notDelete1"
    2) "notDelete2"

    The "Not cell.Value =" does not apply to notDelete2 in this case. You need to change your statement to:

    Code:
    If Not cell.Value = notDelete1 Or Not cell.Value = notDelete2 Then
     

MajorGeeks.Com Menu

Downloads All In One Tweaks \ Android \ Anti-Malware \ Anti-Virus \ Appearance \ Backup \ Browsers \ CD\DVD\Blu-Ray \ Covert Ops \ Drive Utilities \ Drivers \ Graphics \ Internet Tools \ Multimedia \ Networking \ Office Tools \ PC Games \ System Tools \ Mac/Apple/Ipad Downloads

Other News: Top Downloads \ News (Tech) \ Off Base (Other Websites News) \ Way Off Base (Offbeat Stories and Pics)

Social: Facebook \ YouTube \ Twitter \ Tumblr \ Pintrest \ RSS Feeds