macro - show rows ONLY if two columns = each other AND......

[Sayre]

macro - show rows ONLY if two columns = each other AND......
Hello Excel Swammis!

I am in need of your assistance again.

I have an Excel report set up as follows:

Col D - Order #'s
Col G - part #'s
Col H - warehouse code for each part # ("N", "M", etc...)
Col K - Qty of part # ordered
Col L - Qty reserved for that part # on the order

List of orders starts on row 9.

If a particular line on the order is filled, then Col K = Col L for that row.
When the whole order is filled, then Col K = Col L for all rows on that order.

What I need is a macro that identifies FULL ORDERS and hides all other rows. So, if Col K = Col L for all rows adjacent to same order #'s in Col D (AND if Col H has value of "N" or "M" for warehouse code), it will be visible. Any orders with even ONE unfilled line will be hidden.

I am also looking for the reverse of this for a seperate macro. IE: Any orders with even ONE line not completely filled, I want all rows for those orders to be visible and all else hidden (again, only if Col H = "N" or "M").

Is this possible?

Let me know if more info needed, I'll be in front of my monitor for several more hours.

 

[jindon]

OK
Select the order# before you run

Sub test()
Dim r As Range, myOrd, myHidden As String, myNotHidden As String
myOrd = Selection.Cells(1).Value
For Each r In Range("d9", Range("d" & Rows.Count).End(xlUp))
     If (r.Value = myOrd) * (r.Offset(,7).Value = r.Offset(,8).Value) Then
          myNotHidden = myNotHidden & "," & r.Address(0,0)
          If Len(myNotHidden) > 245 Then
               Range(Mid$(myNotHidden,2)).EntireRow.Hidden = False
               myNotHidden = ""
          End If
     Else
          myHidden = myHidden & "," & r.Address(0,0)
          If Len(myHidden) > 245 Then
               Range(Mid$(myHidden,2)).EntireRow.Hidden = True
               myHidden = ""
          End If
     End If
Next
If Len(myNotHidden) Then Range(Mid$(myNotHidden,2)).EntireRow.Hidden = False
If Len(myHidden) Then Range(Mid$(myHidden,2)).EntireRow.Hidden = True
End Sub

 

[Sayre]

Well that seems to have done it! Brilliant zzjasonzz!

This combined with the worksheet_event code Jindon wrote for me will enhance the functionality of my sheet immeasurably, thanks to you both!

Everything I know I picked up slowly over time working on personal projects for my job, and using mainly this site as a resource. Seemed relatively easy to get to the level I am at in this manner, but I'm not sure I will progress much further this way. Did either of you have more formal training to get to your level? Which I would consider to be advanced?

 

[Sayre]

Jindon, thanks for your version of the code. I'd like to try it out as well. The more I analyze and try out different types of codes, the more I learn.

But I need the code to work without selecting anything. Can your code be revised that way? If so, then I'll have another variation to learn from. If you need to move onto other things, no worries, you've been very helpful.

Thanks again, this site is truly amazing.

 

[jindon]

The how do you identify the order# to seach?

 

[Sayre]

I want it to search on all orders listed in column D. All used range of Column D is the search range.

zzzjasonzzz's code does this.

However, that's just for this specific purpose. I think there may be a good use for a variation that calls up one specific order # at a time. Either by selecting an order, or perhaps a cell where an order # is entered and, if order is filled, all lines for the order pop up.

Seeing different variations of similar code really does help me learn so if you feel like revising your code to match the request, I will definitely analyze it, compare to zzzjasonzzz's version, etc... I think this thread and the other one about the worksheet_change event macro are going to be very educational for me after analyze them.

Thanks again Jindon!