首页 文章

根据日期锁定整行

提问于
浏览 次
3

我有提到月份的Cell A1 . 我想比较A2中的日期：最后一个单元格和日期> A1，我想要解锁行，否则锁定 . 以下代码不起作用“

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

Set DestSh = Sheets("Consultant & Volunteer")

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

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

End Sub
excel vba excel-vba

2 回答

  • 2

    这可以简单地用下面的方法完成，但是你必须要小心年份不会改变......而且 lastrow 应该在Z列上 .

    此外，如果工作表不受保护，则无效 .

    Option Explicit

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

    Set DestSh = Sheets("Consultant & Volunteer")

    With DestSh
        'finds the last row with data on A column
        lastrow = .Range("Z" & .Rows.Count).End(xlUp).Row ' <-- EDIT
        'parse all rows
        For i = 6 To lastrow
           'if your conditions are met               
           .Rows(i).Locked = Not (Month(.Cells(i, "Z")) > Month(.Range("A1")))
'           If Month(.Cells(i, 26)) > Month(.Cells(1, 1)) Then
'              .Range("A" & i).EntireRow.Cells.Locked = True 'lock the row
'           End If
        Next i
        .Protect UserInterfaceOnly:=True
    End With
    Set DestSh = Nothing
End Sub
    回复于
  • 0

    循环的替代方案 .

    Dim r As Range, DestSh As Worksheet, lastrow As Long
Set DestSh = Sheets("Consultant & Volunteer")
With DestSh
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
    Set r = .Range("A1:A" & lastrow)
    r.EntireRow.Locked = False
    r.AutoFilter 1, ">" & .Range("A1").Value2
    r.SpecialCells(xlCellTypeVisible).EntireRow.Locked = True
    .AutoFilterMode = False
    .Protect UserInterfaceOnly:=True
End With
    回复于

相关问题