Conditional Format, Filter or VBA Challenge w Border Lines and Blanks Sometimes Present

[ChrisOK]

Having trouble trying to figure out how to do 2 things with the following data with VBA:
1-separate by NSN groupings (draw border above & below grouping from A to Z columns)
2-separate by LOC (location) groupings (EVEN when blanks are present!) (draw border around that group of 'like locations')

The biggest concern is rows like the "WHEEL" where some locations are blank.
This is common, and in this case, I need it to draw a border above "RM11", then scroll downward until a new/different LOC is discovered.

Draw the other separator line/border there to keep the 'like' LOC line items grouped together for further validations. **Drawing a complete box border on all four sides of that group would be ideal, but if that's too hard, I'll settle for an above/below. (border would be around E4:E6)

A..........B..........C...............D...........E......
1Row...Code......NSN.......ITEM...........LOC.....PN....
----------------------------------------------------
2.........1..........258545...WING25.......FB66...12345
3.........2..........258545...WING75.......FB66...12345
4.........1..........258545...WHEEL25.....RM11...34567
5.........2..........258545...WHEEL50...............34567
6.........2..........258545...WHEEL75...............34567
7.........1..........258545...RADAR.........DS24...56789
8.........2..........258545...RADAR.........DS24...56789
------------------------------------------------------
9.........1..........121212...LIFT...........QV19...67891
------------------------------------------------------
10.......1..........771133...HANDLE.......QV19...99335
11.......2..........771133...HANDLE.......QV19...99335
12.......1..........771133...GUN............QV19...72341
13.......2..........771133...GUN............QV19...72341
------------------------------------------------------

End result
1 - the NSN separator lines would look like the above..
2 - the LOC separator lines would ideally border/BOX the following groupings:
.....E2:E3
.....E4:E6
.....E7:E8
.....E9:E13

Tried using macro generator to figure it out - but not sure how to create the logic to:
"Start with X location and continue down the column until a NEW/DIFFERENT location is discovered... once discovered, place a box/border around that grouping...(and include any blanks along the way (jump over them/ignore them) and assume they are part of that same grouping until a NEW/DIFFERENT location is found)

SO FRUSTRATED...
Thanks in advance for any help you can provide!

 

[sijpie]

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><br><SPAN style="color:#00007F">Sub</SPAN> PlaceBorders()<br>    <SPAN style="color:#00007F">Dim</SPAN> rB <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> lR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lLR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> vArr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#007F00">'assuming that database can be large, will _<br>     work with arrays to keep speed</SPAN><br>    <br>    <br>    <SPAN style="color:#007F00">'get last row and column</SPAN><br>    lLR = Range("C1").CurrentRegion.Rows.Count<br>    lC = Range("C1").CurrentRegion.Columns.Count<br>    <br>    <SPAN style="color:#007F00">' Column C has the NSN numbers, load in array</SPAN><br>    vArr = Range("C1:C" & lLR + 1).Value<br>    <SPAN style="color:#007F00">' now we look for changes to NSN numbers in _<br>      the array</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> lR = 1 <SPAN style="color:#00007F">To</SPAN> lLR<br>        <SPAN style="color:#00007F">If</SPAN> vArr(lR, 1) <> vArr(lR + 1, 1) <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> Range(Cells(lR, 1), Cells(lR, lC)).Borders(xlEdgeBottom)<br>                .Color = RGB(200, 0, 0)<br>                .LineStyle = xlContinuous<br>                .Weight = xlMedium<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> lR<br>    <br>    <SPAN style="color:#007F00">' column E has the Location</SPAN><br>    vArr = Range("E1:E" & lLR + 1).Value<br>    <SPAN style="color:#007F00">' now we look for changes to location numbers in _<br>      the array, but exclude the heading</SPAN><br>    lC = 2<br>    <SPAN style="color:#00007F">For</SPAN> lR = 2 <SPAN style="color:#00007F">To</SPAN> lLR<br>        <SPAN style="color:#00007F">If</SPAN> vArr(lR, 1) <> vArr(lR + 1, 1) And _<br>                    (vArr(lR + 1, 1) <> vbNullString Or _<br>                    lR = lLR) <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">' ignore blanks and finish off at last cell</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> Range(Cells(lC, 5), Cells(lR, 5))<br>                .BorderAround LineStyle:=xlContinuous, _<br>                    Color:=RGB(0, 0, 0), _<br>                    Weight:=xlMedium<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            lC = lR + 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> lR<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[ChrisOK]

AB-SO-LUTE-LY fabulous / fabelachtig!! (and thank you very much for messaging out some of the higher level explanations --- I used to be really good at this stuff many moons ago - but have been away from it for SO long I'm forced to refer to my old code books to try to remember what the code means in some cases... I love the way you changed the divider line to dark red bold, this will visually help the eye quickly separate data for the needed validation steps to proceed a bit faster. I clicked "LIKE" in hope it helps you out -- Have a great day! and again>>> "MULTUMESC"/ "DANK U"!!! Best Regards, ChrisOK

 

[sijpie]

I am always for functionality, so the visual outcome is important.

Have a nice day, een goeie dag verder, o ziua buna!