Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel VBA - total line to add border on top and bottom only

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
like image 223
HL8 Avatar asked Mar 16 '12 03:03

HL8


2 Answers

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
like image 118
Jerry Beaucaire Avatar answered Oct 12 '22 23:10

Jerry Beaucaire


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.

like image 26
Doug Glancy Avatar answered Oct 12 '22 23:10

Doug Glancy