eliminating a bunch of rows based on the value of a single r
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: eliminating a bunch of rows based on the value of a single r

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     

    cells example: a spreadsheet with 2 columns (id and flag) only.
    id flag
    a 1
    b 1
    c 2
    c 3
    c 10
    c 10
    d 1
    d 2
    d 6
    d 12
    e 1
    e 7
    e 8
    f 1

    the task is to find the ids that contain a flag greater than 11 and remove all of the rows with those ids. In this case id=d would be removed 5 rows.


  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    South Bend, IN
    Posts
    991
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On Page 12 I found this solution
    to hide all rows that contain "Hide" in Col A
    Also created the opposite (False) to UNhide

    Sub HideRows()
    Dim cell As Range
    For Each cell In Range("A:A")
    If cell.Value = "HIDE" Then
    cell.EntireRow.Hidden = True
    End If
    Next
    End Sub

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    BUt I needed all the rows for the offensive id removed. in this case "d".

    I got it working thanks.

    Now I need the same logic but for "Flags out of order". IE. if the flags are not in ascending order then the id will be deleted.

    Believe this is for a real application. I will explain it if you need.

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK here is the data
    id flag
    a 1
    a 2
    a 3
    b 1
    b 3
    b 2

    I want the macro to figure out that "b"s flags are out of order so it should delete all 3 of b's rows.

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I got it . on to next problem.
    Thanks

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Justin

    To remove all rows of a given letter where the numbers against that letter are out of sequence.
    If your letters are in A2:A1000 and the numbers in B2 to B1000 (ie row 1 is blank) you could use this macro. (it temporarily uses columns UV and IV)

    Application.ScreenUpdating = False
    Range("IU2").FormulaR1C1 = _
    "=IF(RC[-254]=R[1]C,RC[-254],IF(AND(R[1]C[-253]<=RC[-253],RC[-254]=R[1]C[-254]),RC[-254],0))"
    Range("IV2").FormulaR1C1 = _
    "=IF(AND(RC[-255]<>R[-1]C[-255],RC[-255]<>RC[-1]),0,IF(RC[-255]=RC[-1],RC[-1],IF(RC[-255]=R[-1]C[-1],R[-1]C[-1],R[-1]C)))"
    Range("IU2:IV2").AutoFill Destination:=Range("IU2:IV1000"), Type:=xlFillDefault
    For Each cell In Range("IU1:IV1000")
    cell.Formula = cell.Value
    Next
    Dim i As Long
    For i = Cells(Rows.Count, "IV").End(xlUp).Row To 1 Step -1
    If Cells(i, "IV").Value <> 0 Then Cells(i, "IV").EntireRow.Delete
    Next i
    Range("IU1:IV1000").ClearContents
    End Sub

    regards
    Derek


    [ This Message was edited by: Derek on 2002-04-06 01:49 ]

    [ This Message was edited by: Derek on 2002-04-06 21:48 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com