Solved

Delete all grouped rows of data where group does not include row of colored text

Posted on 2014-10-01
6
71 Views
Last Modified: 2014-10-02
Hi.  I've included a small portion of an excel sheet that I build every day for a user.  Rows of data are grouped by an M-number.  Each M-number consists of a number of operations (OPSEQ) and I'm changing the font color where an operation is started before the previous operation is reported complete.  I found out my user only wants to see an entire M-Number grouping (All operations for an M-number) where the font color is blue or red on one of the operations and he has been manually deleting groupings of rows he does not want to see.  

I'm thinking there has to be a way to handle this through programming but I can't wrap my head around this.  Any ideas are welcome.  either delete unnecessary rows or move necessary rows to another sheet?  

thanks labornorpt-snapshot.xls
0
Comment
Question by:valmatic
  • 4
  • 2
6 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40355859
For example, would you want to hide all the rows for the following order numbers?
* M265410
* M265560
* M265580
* M265620
...

Does it matter if cells are colored blue or red, in terms of flagging a particular group?

Lastly, do you need to keep this in Excel 2003?  I note that the sheet is using GET.CELL as part of a named range to derive the cell color.

-Glenn
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40355974
This code should work; you can just add it to your existing workbook:
 
Option Explicit
Sub Remove_Clean_Orders()
    Dim intRows, intColorFlag, r As Integer
    'This will delete rows for orders that have no highlighted operations
    Application.ScreenUpdating = False
    Range("A5").Select
    Do Until ActiveCell.Value = ""
        'if the sum of values in column P is zero, then delete rows
        intRows = Selection.Rows.Count
        ActiveCell.Offset(0, 15).Select
        For r = 0 To intRows - 1
            intColorFlag = intColorFlag + ActiveCell.Offset(r, 0).Value
        Next r
        ActiveCell.Offset(0, -15).Select
        If intColorFlag = 0 Then
            Selection.EntireRow.Delete
            ActiveCell.Offset(-1, 0).Select
        End If
        intColorFlag = 0
        ActiveCell.Offset(1, 0).Select
    Loop
    Application.ScreenUpdating = True
    Range("A4").Select
End Sub

Open in new window


It won't update the alternating grey-bar formatting you've applied, but will remove the sections without any highlighting as you requested.

Regards,
-Glenn
0
 
LVL 7

Author Comment

by:valmatic
ID: 40357526
Hi Glenn,  Sorry so long to respond.  To answer your first post:
1.  yes but I want to delete all of the rows for each of those orders.  2.  Color doesn't matter at this point,  I just need to get rid of all the clean orders so user can print a short version of the report that includes only those orders he needs to get fixed.  3.  I'm running Excel 2010.  

I ran your code on my excel sheet and the end result is great but after running the code, I see all of the blank rows where the clean orders were wiped.  If I save the file, close the spreadsheet and reopen the saved file then the blanks are gone and only the rows I expect are visible.  Any idea why it takes a save/reopen to see the changes?  

I tried this multiple times and got the same result each time.  I also tried removing the blanks manually and via an edit tool but neither works.  thanks
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40357745
It sounds like you have calculation set to manual.  See my attached example file and let me know if you have the same behavior.



-Glenn
EE-labornorpt-snapshot.xls
0
 
LVL 7

Author Closing Comment

by:valmatic
ID: 40358256
Thanks Glenn,  I'm not sure what happened but every test I run now works fine where this morning it was not.  Much appreciated though - exactly what I was looking for.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40358260
You're welcome.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Windows 7 Share with concurrent edits(Excel) 3 33
highlight duplicate entry 16 30
Help with Excel formula 6 38
Excel 2016 loop through 6 0
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now