Draw border after every fifth row in every group

[evacuateNOW]

Hello
I need a border after every fifth record and another border after the group. So far I can do one or the other but not both in vba (ideally) and conditional formatting. The number of columns remain the same (A-G) but the number of rows will change from week to week. The data is sourced from a pivot table from which values are pasted into another worksheet used as a template and formatted to company standards. My sample data is:
A
A
A
A
A
---
A
A
===
B
B
B
===
C
C
C
C
C
---
C
===
D
===
E
===
F
F
F
F
F
===
G
G

I am running Excel 2010 on Win7 Prof. Can you please help? My vba ability is very limited and I've made a mess of what I've cobbled together so far...Does anyone have a solution to assist me please.
Kind regards, EN

 

[Alan_P]

Hi evacuateNOW,

Give this a go see if it works for you...

Sub Border5th()

LastRw = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LastRw

    If Range("A" & i).Value = Range("A" & i + 1).Value Then
        GrpCount = GrpCount + 1
        If GrpCount = 5 Then
            With Range("A" & i & ":G" & i).Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlThin
            End With
        End If
    Else
        With Range("A" & i & ":G" & i).Borders(xlEdgeBottom)
            .LineStyle = xlDouble
            .Weight = xlThick
        End With
        GrpCount = 0
    End If
Next

End Sub

Hope this helps,
Cheers,
Alan.

 

[evacuateNOW]

Hi Alan
Thank you very much, that worked perfectly. I didn't know about GrpCount so will do some reading about that.

One amendment to my request, how would you add a border every five records after the first five? For example if there were 23 records then a thin border will be applied after the 5th, 10th, 15th and 20 records and a thick border after the 23rd. The number of records within each group is unknown.

 

[Alan_P]

Hi evac,

Glad it (kinda) worked for you

Ah sorry just re-read the OP and that is what you asked for just a slight change...

Sub Border5ths()

LastRw = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LastRw

    If Range("A" & i).Value = Range("A" & i + 1).Value Then
        GrpCount = GrpCount + 1
        If GrpCount Mod 5 = 0 Then
            With Range("A" & i & ":G" & i).Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlThin
            End With
        End If
    Else
        With Range("A" & i & ":G" & i).Borders(xlEdgeBottom)
            .LineStyle = xlDouble
            .Weight = xlThick
        End With
        GrpCount = 0
    End If
Next

End Sub

The "GrpCount" is just a variable I set, it can be called anything "Lemon" if you like It's just going round a loop for each cell and adding 1 to it each time the cells match

Cheers,
Alan.

 

[evacuateNOW]

Alan, the result is simply beautiful. Thank you very very much.

I also re-read your post and realised GrpCount was just that, a variable. You've provided a few goodies for me to research and absorb.

With gratitude, EN.

 

[Alan_P]

No prob happy to help thanks for the feedback!