Solved

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

Posted on 2014-10-01
6
78 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

688 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