vba 自动填充动态范围最后一行和最后一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10753925/
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
Autofill Dynamic Range Last Row and Last Column
提问by Alistair Weir
I have a workbook containing multiple sheets of varying sizes. I want to add a total column after the last row and copy the formula across all columns. I have defined the last row and column and the formula appears as expected in the correct place but I receive an error when trying to fill across. How do I correctly reference both dynamic cells for the fill? I'm just using a single sheet for now for testing but will eventually be looping through all the sheets in the book.
我有一个包含多张不同大小的工作簿的工作簿。我想在最后一行之后添加一个总列，并在所有列中复制公式。我已经定义了最后一行和最后一列，公式在正确的位置按预期显示，但在尝试填充时收到错误。如何正确引用两个动态单元格进行填充？我现在只使用一张纸进行测试，但最终会遍历书中的所有纸。
Sub Addtotals()
Dim Bord As Worksheet
Dim LRow As Long
Dim LCol As Long
Dim frmcell As Range
Set Bord = Sheets("Borders")
With Bord
'--> Define last rows and columns
LRow = .Range("A" & Rows.Count).End(xlUp).Row
LCol = .Range("A" & Columns.Count).End(xlToLeft).Column
'--> Add Total text to first column
.Range("A" & LRow).Offset(1, 0).Select
ActiveCell = "Total"
'--> Add formula to next column
Set frmcell = Range("B" & LRow + 1)
frmcell.Formula = "=sum(B2:B" & LRow & ")"
'--> Fill formula across range
frmcell.Select
Selection.AutoFill Destination:=Range(frmcell & LCol), Type:=xlFillDefault
End With
End Sub
Thanks :)
谢谢 ：）
回答by Siddharth Rout
Like this?
像这样？
Option Explicit
Sub Addtotals()
Dim Bord As Worksheet
Dim LRow As Long, LCol As Long
Set Bord = Sheets("Borders")
With Bord
'--> Define last rows and columns
LRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
LCol = .Cells(1, Columns.Count).End(xlToLeft).Column
'--> Add Total text to first column
.Range("A" & LRow).Value = "Total"
'--> Fill formula across range
.Range("B" & LRow & ":" & _
Split(Cells(, LCol).Address, "$")(1) & LRow).Formula = _
"=Sum(B2:B" & LRow - 1 & ")"
End With
End Sub
