1

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
BigBen
  • 46,229
  • 7
  • 24
  • 40
Tomas Perlecky
  • 59
  • 7
  • You want to avoid using `ActiveCell` and `Select` - see [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Otherwise I think you can use `Resize` here. – BigBen Oct 26 '19 at 15:25

1 Answers1

1

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
barneyos
  • 586
  • 2
  • 5
  • 7