Macro's & Cell Borders

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
 
J

Jeff

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
 
D

DGillham

Jeff

Thank you very much for the code, it was exactly what I needed.

Regards

Darre
 
Top