사용자가 행을 추가할지 또는 삭제할지 결정

사용자가 행을 추가할지 또는 삭제할지 결정

사용자가 입력한 데이터를 확인하는 VBA 매크로가 있습니다(데이터 유효성 검사/조건부 형식을 의도적으로 사용하지 않았습니다).

는 사용중을 하고 있습니다.Worksheet_Change코드를 트리거하는 이벤트, 현재 제가 직면한 문제는 행 변경이 있을 때입니다.행 삭제/삽입인지 알 수 없습니다.

그 둘을 구별할 방법이 있습니까?

은 " 다과같범음있정수다습니의할이름을위"와 같이 할 수 .RowMarker =$A$1000

그러면 변경 이벤트의 이 코드는 이전 위치에 대한 이 마커의 위치를 저장하고 변경 사항을 보고합니다(그 후 새 위치를 저장합니다).

Private Sub Worksheet_Change(ByVal Target As Range)
    Static lngRow As Long
    Dim rng1 As Range
    Set rng1 = ThisWorkbook.Names("RowMarker").RefersToRange
    If lngRow = 0 Then
    lngRow = rng1.Row
        Exit Sub
    End If
    If rng1.Row = lngRow Then Exit Sub
    If rng1.Row < lngRow Then
        MsgBox lngRow - rng1.Row & " rows removed"
    Else
        MsgBox rng1.Row - lngRow & " rows added"
    End If
    lngRow = rng1.Row
End Sub

이 코드를 사용해 보십시오.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lNewRowCount As Long

    ActiveSheet.UsedRange
    lNewRowCount = ActiveSheet.UsedRange.Rows.Count

    If lOldRowCount = lNewRowCount Then
    ElseIf lOldRowCount > lNewRowCount Then
        MsgBox ("Row Deleted")
        lOldRowCount = lNewRowCount
    ElseIf lOldRowCount < lNewRowCount Then
        MsgBox ("Row Inserted")
        lOldRowCount = lNewRowCount
    End If

End Sub

또한 이 워크북 모듈에 다음을 추가:

Private Sub Workbook_Open()
    ActiveSheet.UsedRange
    lOldRowCount = ActiveSheet.UsedRange.Rows.Count
End Sub

그리고 이것은 그 자체의 모듈에서:

Public lOldRowCount As Long

코드에서는 1행에 데이터가 있다고 가정합니다.처음 실행할 때마다 잘못된 결과가 표시됩니다. 이는 코드가 lRowCount를 올바른 변수로 설정해야 하기 때문입니다.일단 다 하고 나면 그때부터 괜찮을 겁니다.

공용 변수와 워크시트 열기 이벤트를 사용하지 않으려면 워크시트의 지정된 범위를 어딘가에 사용하고 행 카운트(lRowCount)를 저장할 수 있습니다.

조금 찾아보다가 스스로 해결하기로 했습니다.워크시트 모듈(예: VBA Editor의 Microsoft Excel Objects 아래에 있는 Sheet1)에 다음을 삽입합니다.

Private usedRowsCount As Long 'use private to limit access to var outside of sheet

'Because select occurs before change we can record the current usable row count
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  usedRowsCount = Target.Worksheet.UsedRange.rows.count 'record current row count for row event detection
End Sub

'once row count recorded at selection we can compare the used row count after change occurs
'with the Target.Address we can also detect which row has been added or removed if you need to do further mods on that row
Private Sub Worksheet_Change(ByVal Target As Range)
  If usedRowsCount < Target.Worksheet.UsedRange.rows.count Then
    Debug.Print "Row Added: ", Target.Address
  ElseIf usedRowsCount > Target.Worksheet.UsedRange.rows.count Then
    Debug.Print "Row deleted: ", Target.Address
  End If
End Sub

가정:"둘을 구분"한다는 것은 행을 추가/삭제하는 것과 다른 유형의 변경사항을 구분하는 것을 의미합니다.변경이 행 추가인지 행 삭제인지 확인하는 방법을 의미했다면 아래의 답변을 무시하십시오.

행을 삽입하거나 삭제하는 경우 target.cells.count가 행의 모든 셀이 됩니다.따라서 이 IF 문을 사용하여 캡처할 수 있습니다.파일마다 다를 수 있기 때문에 cells.columns.count를 사용합니다.또한 사용자가 전체 행을 선택하고 "delete"(값을 지우려면)를 누르면 트리거되므로 이에 대한 해결 방법을 코딩해야 합니다.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Cells.Count = Cells.Columns.Count Then
    MsgBox "Row added or deleted"
End If

End Sub

삽입과 삭제를 구분하는 최종 목적 중 일부는 삽입 또는 삭제가 식별된 후 진행 방법을 결정합니다.다음은 아마도 상당히 줄일 수 있지만 가능한 모든 시나리오를 다루려고 노력했습니다.

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo bm_Safe_Exit
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Dim olr As Long, nlr As Long, olc As Long, nlc As Long

    With Target.Parent.Cells
        nlc = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _
                SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        nlr = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Application.Undo    'undo the last change event
        olc = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _
                SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        olr = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Application.Repeat  'redo the last change event
    End With

    If nlr <> olr Or nlc <> olc Then
        Select Case nlr
            Case olr - 1
                Debug.Print "One (1) row has been deleted"
            Case Is < (olr - 1)
                Debug.Print (olr - nlr) & " rows have been deleted"
            Case olr + 1
                Debug.Print "One (1) row has been inserted"
            Case Is > (olr + 1)
                Debug.Print (nlr - olr) & " rows have been inserted"
            Case olr
                Debug.Print "No rows have been deleted or inserted"
            Case Else
                'don't know what else could happen
        End Select
        Select Case nlc
            Case olc - 1
                Debug.Print "One (1) column has been deleted"
            Case Is < (olc - 1)
                Debug.Print (olc - nlc) & " columns have been deleted"
            Case olc + 1
                Debug.Print "One (1) column has been inserted"
            Case Is > (olc + 1)
                Debug.Print (nlc - olc) & " columns have been inserted"
            Case olc
                Debug.Print "No columns have been deleted or inserted"
            Case Else
                'don't know what else could happen
        End Select
    Else
        'deal with standard Intersect(Target, Range) events here
    End If

bm_Safe_Exit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

기본적으로 이 코드는 열 단위로 마지막 셀과 행 단위로 마지막 셀을 식별합니다.그런 다음 마지막 작업을 실행 취소하고 다시 확인합니다.두 결과를 비교하면 행/열이 삽입/삭제되었는지 여부를 확인할 수 있습니다.네 가지 측정이 완료되면 마지막 작업을 다시 수행하여 다른 표준 워크시트_변경 작업을 처리할 수 있습니다.

다음 템플릿을 기반으로 하는 두 가지 접근 방식이 있습니다.

1. 또는 합니다.Range활자를
2. 절대 주소를 사용하여 변수에 지정하여 특수 범위를 "핀"하고 주소 또는 크기를 저장합니다(접근 방식에 따라 다름).
3. 사용자 작업의 하위 유형을 결정하려면 시트 변경 이벤트 핸들러에서 변수를 사용하여 조작합니다.

첫 번째 접근 방식에서는 전체 관심 범위가 변수에 할당되고 범위의 크기가 저장됩니다.그런 다음 시트 변경 이벤트 핸들러에서 다음과 같은 경우를 처리해야 합니다.

• 때 합니다.Address속성 => 고정된 범위가 더 이상 존재하지 않습니다.
• 변경된 셀의 주소가 고정 범위 아래에 있습니다. => 삽입이 => 변수 업데이트
• 고정 범위의 새 크기가 저장된 크기와 다릅니다(예: = > 삭제된 항목, 더 큰 항목 = > 삽입된 항목).

두 번째 접근 방식에서는 변수에 "마커" 범위가 할당되고(아래 예 참조) 범위 주소가 저장되어 어떤 방향으로든 이동 또는 이동을 결정합니다.그런 다음 시트 변경 이벤트 핸들러에서 다음과 같은 경우를 처리해야 합니다.

• 때 합니다.Address속성 => 고정된 "pined" 범위가 더 이상 존재하지 않습니다.
• 변경된 셀의 주소가 "잘못된" 범위 아래에 있습니다 => 삽입이 => 변수 업데이트
• 방향이든 어방향이든있차습니다즉이가느,. 즉,abs(new_row - saved_row) > 0 or abs(new_col-saved_col) > 0=> 고정된 범위가 이동 또는 이동되었습니다.

찬성:

• 사용자 정의 이름이 사용되지 않습니다.
• UsedRange되지 않습니다.
• 고정 범위는 사용자 작업이 1000번째 행 아래에서 발생하지 않는다는 가정 대신 사용자 작업에 따라 업데이트됩니다.

단점:

• 변수를 시트 변경 이벤트 핸들러에서 사용하려면 워크북 열기 이벤트 핸들러에 변수를 할당해야 합니다.
• 변수와 aWithEvents- 객체의 경로는 다음에 할당되어야 합니다.Nothing이벤트를 등록 취소하려면 워크북에서 이벤트 핸들러를 닫습니다.
• 행을 교환하는 대신 범위 값을 변경하기 때문에 정렬 작업을 결정할 수 없습니다.

다음 예제는 두 가지 접근 방식이 모두 작동할 수 있음을 보여줍니다.모듈에서 정의:

Private m_st As Range
Sub set_m_st()
  Set m_st = [$A$10:$F$10]
End Sub
Sub get_m_st()
  MsgBox m_st.Address
End Sub

그럼 실행set_m_st(서브에 커서를 놓고 호출합니다.Run동작) 핀 범위$A$10:$F$10그 위에 행 또는 셀을 삽입하거나 삭제합니다(셀 값 변경과 혼동하지 마십시오).달려.get_m_st고정 범위의 변경된 주소를 확인합니다.고정 범위를 삭제하여 "Object required" 예외를 가져옵니다.get_m_st.

workshot_change 이벤트에서 행 추가 및 삭제를 캡처합니다.

"CurRowCnt"라는 이름의 범위를 만듭니다. 공식: =ROWS(표 1).다음을 사용하여 VBA 코드로 액세스:

CurRowCnt = Evaluate(Application.Names("CurRowCnt").RefersTo)

이 명명된 범위는 항상 한 행을 삽입하거나 삭제한 후의 행 수를 유지합니다.글로벌 또는 모듈 수준 변수를 사용하는 것보다 더 안정적인 CurRowCnt를 제공하며 프로그래밍, 테스트 및 디버깅에 더 적합합니다.

안정성을 위해 CurRowCnt를 사용자 정의 문서 속성에 저장합니다.

ThisWorkbook.CustomDocumentProperties("RowCnt").Value = Evaluate(Application.Names("CurRowCnt").RefersTo)

내 워크시트_이벤트 변경 구조는 다음과 같습니다.

Dim CurRowCnt as Double
CurRowCnt = Evaluate(Application.Names("CurRowCnt").RefersTo)
Select Case CurRowCnt

    '' ########## ROW(S) ADDED
     Case Is > ThisWorkbook.CustomDocumentProperties("RowCnt").Value
         Dim r As Range
         Dim NewRow as Range

         ThisWorkbook.CustomDocumentProperties("RowCnt").Value = _
         Evaluate(Application.Names("CurRowCnt").RefersTo)

         For Each r In Selection.Rows.EntireRow
             Set NewRow = Intersect(Application.Range("Table1"), r)
             'Process new row(s) here
         next r

    '' ########## ROW(S) DELETED
     Case Is < ThisWorkbook.CustomDocumentProperties("RowCnt").Value

         ThisWorkbook.CustomDocumentProperties("RowCnt").Value = _
         Evaluate(Application.Names("CurRowCnt").RefersTo)

         'Process here

    '' ########## CELL CHANGE
    'Case Is = RowCnt

        'Process here

    '' ########## PROCESSING ERROR
    Case Else 'Should happen only on error with CurRowCnt or RowCnt
        'Error msg here

End Select

언급URL : https://stackoverflow.com/questions/7479721/determine-whether-user-is-adding-or-deleting-rows