时间戳

首页 > 解决方案 > vba 的 Excel Lastrow 自动填充问题

excel - vba 的 Excel Lastrow 自动填充问题

问题描述

我在使用 Excel 中的 VBA 自动填充一些代码时遇到问题。我不断收到错误“范围类的自动填充方法失败”，但我不确定代码有什么问题。对我来说看起来不错，但也许我错过了什么？代码如下：

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
    Cancel As Boolean)


A = MsgBox("Do you really want to save the workbook?", vbYesNo)
If A = vbNo Then Cancel = True

Dim lrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A" & lastrow)
Range("A1:A" & lastrow).Select

'Range("D1").Select
'Selection.AutoFill Destination:=Range("D1:D" & lastrow)
'Range("D1:D" & lastrow).Select
'Range("H1").Select
'Selection.AutoFill Destination:=Range("H1:H" & lastrow)
'Range("H1:H" & lastrow).Select
'Range("L1").Select
'Selection.AutoFill Destination:=Range("L1:L" & lastrow)
'Range("L1:L" & lastrow).Select
End Sub

任何正确方向的帮助将不胜感激。（请注意，如果我能正常工作，我只是想修复 Column AI 数字，其余部分将符合要求）。谢谢！！！-D

标签： excelvbaautofill

解决方案

这是你正在尝试的吗？

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim Ret As Variant

    Ret = MsgBox("Do you really want to save the workbook?", vbYesNo)

    If Ret = vbNo Then
        Cancel = True
    Else
        '~~> Make these changes only if user wants to save the workbook
        Dim lrow As Long
        Dim ws As Worksheet

        Set ws = ThisWorkbook.Sheets(1)

        With ws
            '~~> If Col B has data then find the last row in Col B
            lrow = .Range("B" & .Rows.Count).End(xlUp).Row

            .Range("A1:A" & lrow).Formula = .Range("A1").Formula
        End With
    End If
End Sub

推荐阅读