如何修改Excel VBA用户表单代码，将固定范围改为LastRow

核心思路 #

先获取每个工作表的实际最后一行（而非固定行），再将所有硬编码的单元格范围（如C7:C80000）替换为基于最后一行的动态范围。获取最后一行的标准写法是：工作表.Cells(工作表.Rows.Count, "列标").End(xlUp).Row，这个方法会自动定位到指定列的最后一个非空行。

具体修改步骤 #

1. 新增最后一行变量并赋值

在代码开头的变量声明部分，添加两个变量存储工作表的最后一行，然后提前获取对应值：

Dim lastRowRep As Long
Dim lastRowRepc As Long

' 提前获取两个工作表的最后一行（以F列为数据列，可根据实际调整）
lastRowRep = Rep.Cells(Rep.Rows.Count, "F").End(xlUp).Row
lastRowRepc = Repc.Cells(Repc.Rows.Count, "F").End(xlUp).Row

2. 修改CountIfs中的固定范围

把原来的固定行范围替换为动态拼接的范围：

• 原代码：

  fwr = Application.WorksheetFunction.CountIfs(Rep.Range("C7:C80000"), Me.CbInvStore.Value, Rep.Range("F7:F80000"), Me.TbInvNo.Value)
  fwrc = Application.WorksheetFunction.CountIfs(Repc.Range("B9:B30000"), Me.CbInvStore.Value, Repc.Range("D9:D30000"), Me.TbInvNo.Value)
  

• 修改后：

  fwr = Application.WorksheetFunction.CountIfs(Rep.Range("C7:C" & lastRowRep), Me.CbInvStore.Value, Rep.Range("F7:F" & lastRowRep), Me.TbInvNo.Value)
  fwrc = Application.WorksheetFunction.CountIfs(Repc.Range("B9:B" & lastRowRepc), Me.CbInvStore.Value, Repc.Range("D9:D" & lastRowRepc), Me.TbInvNo.Value)
  

3. 修改筛选后获取最后一行的代码

将从固定行往上查找的逻辑，改为从工作表末尾定位：

• 原代码（Rep表部分）：

  lastrow = .Range("F80000").End(xlUp).Row + 1
  

• 修改后：

  lastrow = .Cells(.Rows.Count, "F").End(xlUp).Row + 1
  

• 原代码（Repc表部分）：

  ss = .Range("f30000").End(xlUp).Row
  

• 修改后：

  ss = .Cells(.Rows.Count, "F").End(xlUp).Row
  

完整修改后的代码 #

ThisWorkbook.Activate
Dim X As Long
Dim xx As Long
Dim fwr As Integer
Dim fwo As Integer
Dim lastRowRep As Long ' 存储Rep表最后一行
Dim lastRowRepc As Long ' 存储Repc表最后一行

mer = " Data Error : "
mtk = " Data Duplicate Error : "

' 提前获取两个工作表的最后一行
lastRowRep = Rep.Cells(Rep.Rows.Count, "F").End(xlUp).Row
lastRowRepc = Repc.Cells(Repc.Rows.Count, "F").End(xlUp).Row

' 动态范围的CountIfs
fwr = Application.WorksheetFunction.CountIfs(Rep.Range("C7:C" & lastRowRep), Me.CbInvStore.Value, Rep.Range("F7:F" & lastRowRep), Me.TbInvNo.Value)
fwrc = Application.WorksheetFunction.CountIfs(Repc.Range("B9:B" & lastRowRepc), Me.CbInvStore.Value, Repc.Range("D9:D" & lastRowRepc), Me.TbInvNo.Value)
fwo = Me.ListBox1.ListCount - 1 ' no of items
'==========================================================

If fwr < 1 Then:  MsgBox mer & vbCrLf & vbCrLf & "Please enter the invoice number ", vbCritical + vbMsgBoxLeft, "Inventory Program ": Exit Sub
If fwrs < 1 Then:  MsgBox mer & vbCrLf & vbCrLf & "Please enter the invoice number ", vbCritical + vbMsgBoxLeft, "Inventory Program ": Exit Sub

If fwrc < 1 Then:  MsgBox mer & vbCrLf & vbCrLf & " Invoice number not found in the statement of the client's account", vbCritical + vbMsgBoxLeft, "Inventory Program ": Exit Sub
If fwrc > 1 Then:  MsgBox mtk & vbCrLf & vbCrLf & " Duplicate invoice number " & fwrc & " Once ", vbCritical + vbMsgBoxLeft, "Inventory Program ": Exit Sub
If fwo < 1 Then:  MsgBox mer & vbCrLf & vbCrLf & " There are no items to be adjusted ", vbCritical + vbMsgBoxLeft, "Inventory Program ": Exit Sub

confir = MsgBox("Would you like to save the change...  ?", vbOKCancel, "Inventory Program")
If confir = vbCancel Then: Exit Sub

Application.ScreenUpdating = False

On Error GoTo 1
       
' this is worksheet Report 
With Rep
    .Select
    .Unprotect ("0000")
    AutoFilterMode = False
    .Range("$C$6:$Z$6").AutoFilter field:=2, Criteria1:=Me.CbInvStore.Value
    .Range("$C$6:$Z$6").AutoFilter field:=5, Criteria1:=Me.TbInvNo.Value

    ' 动态获取最后一行
    lastrow = .Cells(.Rows.Count, "F").End(xlUp).Row + 1
    .Range("$C$6:$Z$6").AutoFilter

    If fwo = fwr Then
        X = lastrow - fwr
        xx = lastrow - 1
    ElseIf fwo > fwr Then
        d = fwo - fwr
        X = lastrow - fwr
        xx = lastrow + d - 1
        .Range(Cells(lastrow, "c"), Cells(xx, "c")).Select
        Selection.EntireRow.Insert, CopyOrigin:=xlFormatFromLeftOrAbove
    ElseIf fwo < fwr Then
        X = lastrow - fwr
        xx = X + fwo - 1
        Y = xx + 1
        z = lastrow - 1
        .Range(Cells(Y, "c"), Cells(z, "c")).Select
        Selection.EntireRow.Delete
    Else
        MsgBox "There is somthing wrong. will be exit ", vbCritical, "Inventory Program  "
    End If

    Me.TbDate.Text = Format(Me.TbDate.Text, "dd/mm/yyyy")

    .Range(Rep.Cells(X, "B"), Rep.Cells(xx, "B")) = Me.comDepartment.Value   
    .Range(Rep.Cells(X, "C"), Rep.Cells(xx, "C")) = Me.CbInvStore.Value   
    .Range(Rep.Cells(X, "D"), Rep.Cells(xx, "D")) = Me.CbPayment.Value 
    .Range(Rep.Cells(X, "E"), Rep.Cells(xx, "E")) = Me.Lbl_typ.Caption   
    .Range(Rep.Cells(X, "F"), Rep.Cells(xx, "F")) = Format(Me.TbInvNo.Value, "00000")  
           
    .Range(Rep.Cells(X, "G"), Rep.Cells(xx, "G")) = Me.TbDate.Value    'date
    .Range(Rep.Cells(X, "H"), Rep.Cells(xx, "H")) = Me.CbCustomerName.Value  
    .Cells(X, "I") = Me.TbTotalNetPrice.Value   'balance
    .Range(Rep.Cells(X, "T"), Rep.Cells(xx, "T")) = Users.Range("aw6").Value   

    Inv.Unprotect ("0000")
   
    .Protect Password:= ("0000")
End With
'=========================================================================================
' This worksheet Report Customer 
With Repc
    .Select
    .Unprotect ("8521")
    AutoFilterMode = False
    .Range("$B$8:$P$8").AutoFilter field:=1, Criteria1:=Me.CbInvStore.Value
    .Range("$B$8:$P$8").AutoFilter field:=3, Criteria1:=Me.TbInvNo.Value

    ' 动态获取最后一行
    ss = .Cells(.Rows.Count, "F").End(xlUp).Row
    .Range("$d$8:$r$8").AutoFilter

    .Cells(ss, "F").Value = Me.CbCustomerName.Value  'customer
    .Cells(ss, "B").Value = Me.CbInvStore.Value  'inv store
    .Cells(ss, "C").Value = Me.TbDate.Value  'date
    .Cells(ss, "K").Value = Me.CbPayment.Value

    .Cells(ss, "O").Value = Me.CbMrName.Value  'mr
    .Cells(ss, "P").Value = Users.Range("aw6").Value  'user

    ' CASE RETURN=====================
    If Me.CbInvStore.Value = Data.Range("BF9") Or Me.CbInvStore.Value = Data.Range("BF10") Then
        .Cells(ss, "I") = Me.TbTotalNetPrice.Value    
    Else
        .Cells(ss, "G") = Me.TbTotalNetPrice.Value   
    End If

    If Me.CbPayment.Value = "Cash" Then
        .Cells(ss, "H") = Me.TbTotalNetPrice.Value  
    End If
    .Protect Password:= ("0000")
End With

confir = MsgBox(" Change successfully Saved", vbInformation, "Inventory Program")

1 Application.ScreenUpdating = True

注意事项 #

• 如果数据并非存储在F列，需要将代码中Cells(.Rows.Count, "F")的"F"替换为实际的数据列标（比如数据在C列就改成"C"）。
• 动态获取最后一行的方法能自动适配数据行数变化，避免固定范围导致的漏查或无效计算。

内容的提问来源于stack exchange，提问作者Mohamed Amin