VBA to Add border around a block of cells based on each change in value for Col A

[richardstory]

Hi,

I'm looking for a simple VBA to loop adding a Thick (outside edge only) Border around a range of cells in a list (i.e. Col A to Col H) for the rows in which the value of Col A is the same, i.e. Rows 2-4="One", Rows 5-10="Two", therefore this would create the Border around range A2:H4, A5:H10 and so on.
NB (list is sorted by Col A so all like data is together)

Any assistance appreciated.

 

[Peter_SSs]

Give this a try with a copy of your workbook.

Sub Add_Borders()
  Dim a As Variant
  Dim i As Long, j As Long, fr As Long
  
  With Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1))
    a = .Value
    fr = 2
    For i = 2 To UBound(a) - 1
      Do While a(i + j, 1) = a(i, 1) And i + j <= UBound(a)
        j = j + 1
      Loop
      .Cells(fr, 1).Resize(j, 8).BorderAround xlContinuous, xlThick, xlAutomatic
      fr = fr + j
      i = i + j - 1
      j = 0
    Next i
  End With
End Sub

My result:

 

[richardstory]

Sub Add_Borders() Dim a As Variant Dim i As Long, j As Long, fr As Long With Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)) a = .Value fr = 2 For i = 2 To UBound(a) - 1 Do While a(i + j, 1) = a(i, 1) And i + j <= UBound(a) j = j + 1 Loop .Cells(fr, 1).Resize(j, 8).BorderAround xlContinuous, xlThick, xlAutomatic fr = fr + j i = i + j - 1 j = 0 Next i End With End Sub

thanks, just does the job.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.