Excel VBA - 仅在顶部和底部添加边框的总行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9731587/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - total line to add border on top and bottom only
提问by HL8
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.
例如。我有第 2 行到第 3 行和第 3-4 列的数据，然后我添加了一条总行，该行对第 5 行中的第 2-3 行求和。
I want to add a border line on top and bottom of row 5 and only upto column 4.
我想在第 5 行的顶部和底部添加一条边界线，最多只能添加到第 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?
我可以使用变量 LastRow + 2（注意我在最后一行数据和总行位置之间有一个空行）和 LastColumn 一些如何在 Range("A5:D5").Select 中，因为这每次都是可变的？
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
采纳答案by Jerry Beaucaire
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:
我认为 NexttRow 的事情仍然是一个好主意，代码也可以简化，这会添加总和并将总和行从 row2 格式化到数据的底部，无论在哪里：
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
回答by Doug Glancy
You don't really need LastRow or LastCol variables. Just refer to the last row of your range like this:
您实际上并不需要 LastRow 或 LastCol 变量。只需像这样参考范围的最后一行：
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.
您可以将其概括为将范围传递给的子例程。
