Solved

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

Posted on 2014-10-01
6
73 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

831 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