Solved

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

Posted on 2014-10-01
6
68 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

760 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

21 Experts available now in Live!

Get 1:1 Help Now