I want to add a border line on top and a border line on bottom of the total line
Eg. I have data from rows 2 to 3 and columns 3-4, I have then add a total line which sums line 2-3 in row 5.
I want to add a border line on top and bottom of row 5 and only upto column 4.
Can I use variables LastRow + 2 (note I have a blank line between the last row of data and where the total line is) and LastColumn some how in Range("A5:D5").Select as this will be varible each time?
My current Code:
Range("A5:D5").Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
I think the NexttRow thing is still a good idea, and the code can be simplified down as well, this adds the sum and formats the sum row from row2 to the bottom of the data, wherever that may be:
NR = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("C" & NR, "D" & NR).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
With Range("A" & NR, "D" & NR)
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
End With
You don't really need LastRow or LastCol variables. Just refer to the last row of your range like this:
With Range("A5:D5")
With .Rows(.Rows.Count)
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End With
You could generalize this into a subroutine that you pass a range to.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With