I am trying to border around the outside of a range. I have the example attached. The range will always start in column"C" but will be different lengths. I am looking for VBA to do this. Thanks for help
I am trying to border around the outside of a range. I have the example attached. The range will always start in column"C" but will be different lengths. I am looking for VBA to do this. Thanks for help
Hi Matt,
Try this:
Sub GetBord(): Dim Q As Range, U As Range, lr As Long, lc As Long, r As Long, c As Long Dim i As Long, j As Long, k As Long Set U = ActiveSheet.UsedRange: r = U.row: c = U.Column lr = r + U.Rows.count: Do Until Cells(lr, c) <> "": lr = lr - 1: Loop lc = c + U.Columns.count:: Do Until Cells(lr, lc) <> "": lc = lc - 1: Loop Set Q = Range(Cells(r + 1, c), Cells(lr, lc)): GoSub Bordm Set Q = Range(Cells(r + 1, c), Cells(r + 1, lc)): GoSub Bordm i = c + 1: Do Until InStr(1, Cells(i, c), "Total"): i = i + 1: Loop Set Q = Range(Cells(i, c), Cells(i, lc)): GoSub Bordm i = i + 1: Do Until InStr(1, Cells(i, c), "Total"): i = i + 1: Loop Set Q = Range(Cells(i, c), Cells(i, lc)): GoSub Bordm Set Q = Range(Cells(lr, c), Cells(lr, lc)): GoSub Bordm Set Q = Range(Cells(r + 1, c + 1), Cells(lr, c + 1)): GoSub Bordm Set Q = Range(Cells(r + 1, lc), Cells(lr, lc)): GoSub Bordm Exit Sub Bordm: With Q.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Q.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Q.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Q.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With: Return End Sub
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks