Help - Need To Add Borderline Dependant On Result/Duplication

[TkdKidSnake]

Hi all,

I am trying to add several border lines to the bottom of a row from A to AI start from row 12.

There is probably a few things you need to know

1. Starting from row 11 each data section is two rows deep
2. The data section could be repeated and if this is the case do not insert border until it changes
3. The border line needs to be added underneath the second line i.e. to split the different types of data
4. The data is in every odd row in column R starting from cell R 11

I have attached two pictures one show the raw version as it is now and the other showing what I am after

If anyone can offer a solution to this it would be most appreciated.


Thanks in advance!


001 - Sample.jpg
002 - FinishedSpreasheet.jpg

[AndyLitch]

i'd strongly recommend you reorganise your database.. Column R has both the part number and description which should really be separated into two different columns...........if you do that Excel can do the rest

[TkdKidSnake]

i'd strongly recommend you reorganise your database.. Column R has both the part number and description which should really be separated into two different columns...........if you do that Excel can do the rest

Unfortunately this is not possible as this is how the data is extracted from our MRP system but thanks for your comments

[AndyLitch]

I know what you mean... It's very common for database query tools to extract data in ways that make it difficult to use...
I would recommend a macro to reorganise your data after importing.......... Borders are just a visual aid and serve no real purpose... If you have thousands of records they won't help very much... Does that make sense ??

[TkdKidSnake]

I know what you mean... It's very common for database query tools to extract data in ways that make it difficult to use...
I would recommend a macro to reorganise your data after importing.......... Borders are just a visual aid and serve no real purpose... If you have thousands of records they won't help very much... Does that make sense ??

I am trying to leave as much as the data as intact as possible and these are what are used to raise orders so need to be printed so that they are easily manageable and you rightly suggested the borders are just visual aids. This is important to separate the data which would leave less room for error.

[AndyLitch]

OK try this

Sub Test()
Dim LastRow As Long, Y As Long

    LastRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
    For Y = 11 To LastRow Step 2
    Range("A" & Y & ":AG" & Y).Borders(xlDiagonalDown).LineStyle = xlNone
    Range("A" & Y & ":AG" & Y).Borders(xlDiagonalUp).LineStyle = xlNone
    With Range("A" & Y & ":AG" & Y).Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With
    With Range("A" & Y & ":AG" & Y).Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With
    With Range("A" & Y & ":AG" & Y).Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With
    With Range("A" & Y & ":AG" & Y).Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With
    Range("A" & Y & ":AG" & Y).Borders(xlInsideVertical).LineStyle = xlNone
    Range("A" & Y & ":AG" & Y).Borders(xlInsideHorizontal).LineStyle = xlNone
    Next Y

End Sub

[TkdKidSnake]

OK try this

Sub Test()
Dim LastRow As Long, Y As Long

    LastRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
    For Y = 11 To LastRow Step 2
    Range("A" & Y & ":AG" & Y).Borders(xlDiagonalDown).LineStyle = xlNone
    Range("A" & Y & ":AG" & Y).Borders(xlDiagonalUp).LineStyle = xlNone
    With Range("A" & Y & ":AG" & Y).Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With
    With Range("A" & Y & ":AG" & Y).Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With
    With Range("A" & Y & ":AG" & Y).Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With
    With Range("A" & Y & ":AG" & Y).Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With
    Range("A" & Y & ":AG" & Y).Borders(xlInsideVertical).LineStyle = xlNone
    Range("A" & Y & ":AG" & Y).Borders(xlInsideHorizontal).LineStyle = xlNone
    Next Y

End Sub

I have edited what you have suggested slightly however this is now underlining all underneath the description whereas I only wanted to underline when the number changes and then the last one of the duplicates so that they are groups together.

All I need is now is something like the example below:
If Cell R11 = Cell R13 then do not insert line at the bottom of row 12, if Cell R13 > Cell R11 then insert line at the bottom of Row 12 this then needs to loop to then end of the worksheet

Edited VBA coding
Sub SingleLine()
Dim LastRow As Long, Y As Long


LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For Y = 12 To LastRow - 2 Step 2
Range("A" & Y & ":AG" & Y).Borders(xlDiagonalDown).LineStyle = xlNone
Range("A" & Y & ":AG" & Y).Borders(xlDiagonalUp).LineStyle = xlNone


With Range("A" & Y & ":AI" & Y).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With

Range("A" & Y & ":AG" & Y).Borders(xlInsideVertical).LineStyle = xlNone
Range("A" & Y & ":AG" & Y).Borders(xlInsideHorizontal).LineStyle = xlNone
Next Y


End Sub

If you have any idea's how to achieve this please advise. Thanks for your help so far!

[AndyLitch]

Something along the lines of

If Range("R" & Y + 1).Value > Range("R" & Y - 1).Value Then
    Rows(Y & ":" & Y).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Endif

[TkdKidSnake]

Something along the lines of

If Range("R" & Y + 1).Value > Range("R" & Y - 1).Value Then
    Rows(Y & ":" & Y).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Endif

Should this be before the previous coding?

[AndyLitch]

Needs to be inside the FOR/NEXT loop.. Probably best straight after the FOR..

[TkdKidSnake]

Needs to be inside the FOR/NEXT loop.. Probably best straight after the FOR..

Edited all and this works thanks for all your help.

'This routine adds the thin line below each of the description lines & groups duplicates
'
'Checks for end of worksheet
Dim LastRow As Long, Y As Long
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For Y = 12 To LastRow Step 2
'
'Checks for duplicates
If Range("R" & Y + 1).Value > Range("R" & Y - 1).Value Then
'
'Adds single thin line
Range("A" & Y & ":AG" & Y).Borders(xlDiagonalDown).LineStyle = xlNone
Range("A" & Y & ":AG" & Y).Borders(xlDiagonalUp).LineStyle = xlNone
With Range("A" & Y & ":AI" & Y).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
'
'Check for duplicates EndIf
End If


Thanks again

[cyncerq]

This helped me out enormously!