• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 82
  • Last Modified:

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

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
valmatic
Asked:
valmatic
  • 4
  • 2
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
valmaticAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Glenn RayExcel VBA DeveloperCommented:
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
 
valmaticAuthor Commented:
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
 
Glenn RayExcel VBA DeveloperCommented:
You're welcome.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now