How to put borders around a range of merged and non-merged cells

Question

I need a macro that puts a border in a range of cells from A to F, based on the formatting in the A column. In the A column, cells can be merged with multiple cells below it, or can be just a single cell. I have written a VBA code to put a border around the non-blank cells in column A, but dont know how to expand it to the other 5 columns (from B to F). See the pictures to better understand what I have written and what I need.

How the Data looks like:Data

What my code does:WhatMyCodeDoes

What I want it to do:WhatIWantItToDo

My code:

Sub Borders()
Dim linestyle As Variant, line   As Variant
Range("A1").Select
Do While ActiveCell.Address <> Range("A65536").End(xlUp).Offset(1, 0).Address
If ActiveCell.Value > 0 Then
 linestyle = Array(xlDiagonalDown, xlDiagonalUp, xlInsideVertical, xlInsideHorizontal)
line = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)
For I = 0 To UBound(linestyle)
Selection.Borders(linestyle(I)).linestyle = xlNone
    With Selection.Borders(line(I))
        .linestyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Next I
Else
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

You want to avoid using ActiveCell and Select - see this question. Otherwise I think you can use Resize here. — BigBen

barneyos barneyos · Accepted Answer · 2019-10-26T16:34:39

You may change only two lines and will be OK.

'Range("A1").Select
Range("A1").Resize(1, 6).Select
' ...

'ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(1, 0).Resize(1, 6).Select

How to put borders around a range of merged and non-merged cells

1 Answers