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

Sayre

Board Regular
Joined
Apr 12, 2005
Messages
180
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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Sayre

Board Regular
Joined
Apr 12, 2005
Messages
180
revised req-show rows ONLY if two columns = each other AND..

Can anyone take a crack at this? If I can just get the first macro I may be able to figure out how to do the reverse version macro. Here again is a summary of my request (slightly revised for more clarity).

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 , all of those lines will be visible (exception: if a row does not have value of "N" or "M" in Col H, that line will be hidden) .

Let me know if this is too confusing. I am struggling to articulate this in a simple way....
 

Sayre

Board Regular
Joined
Apr 12, 2005
Messages
180
I've got to try to request this again, is crucial to what I am trying to do.

Can anyone help? Is Jindon around? This request seems right up Jindon's alley. Jindon recently helped me with a similar need and did a sectacualr job on it.

Also, I'll simplify the request further by removing the need to factor in Col H warehouse designation. So here again is a revised request:

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 , all of those lines will be visible.

I need to omit rows where Col H does not say "N" or "M" but I think I can handle that with another macro after I take care of this portion. Can anyone help?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
This requires Worksheet_Change event, but you already have another one.

Post you current code in the sheet module, otherwise we can not write any.
 

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
is this what you need?

Code:
For i = 9 To Range("D9").End(xlDown).Row
    If ((Range("K" & i) = Range("L" & i)) And ((Range("H" & i) = "M") Or (Range("H" & i) = "N"))) Then
        Rows(i).EntireRow.Hidden = False
    Else
        Rows(i).EntireRow.Hidden = True
    End If
Next i

Edited to change AND to OR
 

Sayre

Board Regular
Joined
Apr 12, 2005
Messages
180
Thanks zzjasonzz, but it does not seem to be doing what I need.

For the sake of simplicity, I have revised your code to omit the "M" and "N" criteria (I'll take care of these aspects later). So here is the code new code

Sub Show_FullOrders_Only ()
For i = 9 To Range("D9").End(xlDown).Row
If ((Range("K" & i) = Range("L" & i))) Then
Rows(i).EntireRow.Hidden = False
Else
Rows(i).EntireRow.Hidden = True
End If
Next i
End Sub

I tried it out on the very first order in my list (which is five rows deep starting row 9). The order # is listed in all 5 rows of Col D. All qty's are full on this order except for one. Therefore, all 5 rows should end up hidden when the macro is finished. But that did not happen. It left all rows visible except the one that was not full.

What I am looking for is basically to show "all or nothing". If order is completely full, show me all rows for that order. If even one item is not full, then hide the whole order.

Is this making more sense?

Jindon, this one is not a worksheet_event macro. I need to connect it to hotkey or button.
 

Sayre

Board Regular
Joined
Apr 12, 2005
Messages
180
Excel_Example.rtf
[/img]
 

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
Oh ok, I misunderstood you..

try this

Code:
Dim i, j
Dim notfull
Dim currentordernum

notfull = 0
For j = 9 To Range("D9").End(xlDown).Row

    currentordernum = Range("D" & j).Value
    
    For i = 9 To Range("D9").End(xlDown).Row
        If Range("D" & i).Value = currentordernum Then
            If Range("K" & i) <> Range("L" & i) Then
                notfull = 1
            End If
        End If
    Next i

    If notfull = 1 Then
        Rows(j).EntireRow.Hidden = True
    Else
        Rows(j).EntireRow.Hidden = False
    End If

    notfull = 0
Next j

theres def a better way to do it but works on my test data
 

Forum statistics

Threads
1,181,416
Messages
5,929,790
Members
436,694
Latest member
dpatete

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top