excel - 如何锁定一列直到它的最后一行有数据

Question

我在单元格 A1 中提到了日期，例如“五月”。

我现在正在尝试使用 Z 列锁定第 2 行，该列提到每个员工的加入日期并将其与 A1 进行比较。

如果这个单元格 Z 的月份 > A1，那么我正在尝试锁定该行。不知道该怎么办。

下面的代码没有帮助:

Sub Lockrow()
Dim DestSh As Worksheet
Dim lastrow As Long
Dim i As Integer

Set DestSh = Sheets("Consultant & Teacher")

With DestSh
    'finds the last row with data on A column
    lastrow = Range("A65536").End(xlUp).Row

    'parse all rows
    For i = 6 To lastrow
       'if your conditions are met
       If Month(.Cells(i, 26)) > Month(.Cells(1, 2)) Then
          .Range("A" & i).EntireRow.Cells.Locked = True 'lock the row
       End If
    Next i
End With

End Sub

Answer 1

这是你正在尝试的吗？

Sub Sample()
    Dim DestSh As Worksheet
    Dim lastrow As Long

    '~~> Change this as applicable
    Set DestSh = Sheets("Sheet1")

    With DestSh
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lastrow = .Columns("A:C").Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            MsgBox "Insufficient rows"
            Exit Sub
        End If

        .Unprotect "MyPassword"
        .Cells.Locked = False

        .Range("A6:C" & lastrow).Locked = True

        .Protect "MyPassword"
    End With
End Sub