Macro's & Cell Borders

  • Thread starter Thread starter DGillham
  • Start date Start date
D

DGillham

I hope someone can help me!

I have put together a macro that produces a report but there is on
thing that is lacking, cell borders! Does anyone have a macro/VBA cod
that will add a cell border to only those rows that have data in them.
The reason I ask is that the reports could be anything from 20 to 50
rows and having to add the borders afterwoods is a nuisance.

Can anyone help (please keep it simple if possible, I'm new to this!)

Darre
 
Hi,

This code isn't the prettiest, but should work.
(Try on test data)

jeff

Sub BorderRows()
Dim lastrow, lastcol As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
ActiveCell.SpecialCells(xlLastCell).Select
lastrcol = Selection.Column
For j = 1 To lastrow
flg = False
For k = 1 To lastrcol
If Range(Cells(j, k), Cells(j, k)) <> "" Then flg = True
Next k
If flg = True Then
Range(Cells(j, 1), Cells(j, lastrcol)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
End If
Next j
End Sub
 
Back
Top