Macro to place borders between groups or rows
 

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

Macro to place borders between groups or rows
 

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
---------------
Jacob Skaria


"PVANS" wrote:

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

Macro to place borders between groups or rows
 

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:D" & 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" wrote:

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

Macro to place borders between groups or rows
 

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

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"PVANS" wrote:

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