Macro to place borders between groups or rows

[PVANS]

Good morning,

I have a worksheet with rows of data. Column D identifies the client account
number. There are more often more than 1 row of data for the same client
account. The worksheet is already sorted so that the the data is sorted into
the various different clients, ie:
D E F
Client # Description Amount
LBL001 Coke 100
LBL001 Pepsi 50
LBL002 Coke 75
LBL002 Coke 80
LBL002 Pepsi 100
LBL003 Sprite 30
LBL003 Coke 50

I would like a macro to run that places a "Thich Bottom Border" after each
group of like client accounts. Could someone please assist me in a method
that checks whether Column D in one row is the same as the Column D in the
row below, if so, it checks the next row below that, if not, it places a
"thick Bottom Border" to identify that that is the end of one group and start
of the next.

I would really appreciate the help, thank you

Regards

 

[Jacob Skaria]

Try the below which works on the active sheet.

Sub MyMacro()
Dim lngRow As Long
For lngRow = 2 To Cells(Rows.Count, "D").End(xlUp).Row
If Range("D" & lngRow) <> Range("D" & lngRow + 1) Then
With Range("D" & lngRow).Resize(1, 3).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If
Next
End Sub

If this post helps click Yes

 

[Mike H]

Hi,

Try this macro. I have assumed the data are already sorted

Sub Marine()
lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
Set MyRange = Range("D2" & lastrow)
For Each c In MyRange
If c.Value <> c.Offset(1).Value Then
Set c = c.Resize(, 3)
With c.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
End If
Next
End Sub

Mike

 

[PVANS]

Jacob,

as usual you have made it look so simple , thank you. Works absolutely
perfectly.

Thank you so much, hope you have a pleasant weekend.

Regards
Paul