VBA to format border on every nth cell

[ExceL0ver]

Hi,

I have multiple data sets in columns A-G of 13 rows each, which are stacked on top of each other. The data sets are dynamic and there can be between 1 to 40 sets of data. The cells in the first row of the whole data set are headers.

I currently have a macro that draws this data from the relevant sources so I'd prefer not to use conditional formatting as a solution. I want to add more code to my current macro so that the header, and then every last row of each data set has a bold line applied under it, until the last set of data i.e., on rows 1, 14, 27, 40, etc.

I am currently using the code below, but it only works with a certain number of ranges and beyond this it throws a Run-time '1004': Method 'Range' of object '_Global' failed error, so I've had to split the ranges and repeat the code with the remaining ranges. I also don't know how to make this dynamic to stop the application of formatting after the last set of data.

Range("A1:G1, A14:G14, A27:G27, A40:G40, A53:G53, A66:G66, A79:G79, A92:G92, A105:G105, A118:G118, A131:G131, A144:G144, A157:G157, A170:G170, A183:G183, A196:G196, A209:G209, A222:G222, A235:G235, A248:G248, A261:G261, A274:G274, A287:G287, A300:G300, A313:G313, A326:G326, A339:G339, A352:G352, A365:G365, A378:G378, A391:G391, A404:G404, A417:G417, A430:G430, A443:G443, A456:G456, A469:G469, A482:G482, A495:G495, A508:G508").Select
       With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With

I've been trying to use a loop for this instead, but can't get it to work. What is the cleanest solution for this?

Thanks

 

[DanteAmor]

Hi @ExceL0ver .
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try the following code, it will put the line in each set taking as reference the last row with data from column A.

Sub Format_Border_v1()
  Dim i As Long
  Range("A:G").Borders.LineStyle = xlNone
  For i = 1 To Range("A" & Rows.Count).End(3).Row Step 13
    With Range("A" & i).Resize(1, 7).Borders(xlEdgeBottom)
      .LineStyle = xlContinuous
      .Weight = xlThick
    End With
  Next
End Sub

If you don't want the last line then try this:

Sub Format_Border_v1()
  Dim i As Long
  Range("A:G").Borders.LineStyle = xlNone
  For i = 1 To Range("A" & Rows.Count).End(3).Row - 1 Step 13
    With Range("A" & i).Resize(1, 7).Borders(xlEdgeBottom)
      .LineStyle = xlContinuous
      .Weight = xlThick
    End With
  Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[ExceL0ver]

Hi @ExceL0ver .
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try the following code, it will put the line in each set taking as reference the last row with data from column A.

Sub Format_Border_v1()
  Dim i As Long
  Range("A:G").Borders.LineStyle = xlNone
  For i = 1 To Range("A" & Rows.Count).End(3).Row Step 13
    With Range("A" & i).Resize(1, 7).Borders(xlEdgeBottom)
      .LineStyle = xlContinuous
      .Weight = xlThick
    End With
  Next
End Sub

If you don't want the last line then try this:

Sub Format_Border_v1()
  Dim i As Long
  Range("A:G").Borders.LineStyle = xlNone
  For i = 1 To Range("A" & Rows.Count).End(3).Row - 1 Step 13
    With Range("A" & i).Resize(1, 7).Borders(xlEdgeBottom)
      .LineStyle = xlContinuous
      .Weight = xlThick
    End With
  Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

Hello Dante Amor,

Thank you for your welcome, and thank you so much for your solution; it's exactly what I required and works perfectly!

Have a lovely day!