自动填充动态范围最后一行和最后一列 [英] Autofill Dynamic Range Last Row and Last Column

问题描述

我有一个工作簿，其中包含多张不同尺寸的工作表.我想在最后一行之后添加一个总列，并在所有列中复制公式.我已经定义了最后一行和最后一列，公式在正确的位置按预期显示，但在尝试填充时收到错误.如何正确引用两个动态单元格进行填充?我现在只使用一张纸进行测试，但最终会遍历书中的所有纸.

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

谢谢:)

推荐答案

喜欢这个吗?

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

这篇关于自动填充动态范围最后一行和最后一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！