How to Combine these Two Statements?

[cmkarnes]

Hello again, despite trying numerous statements, I am not successful in combining these two statements in vba. The first statement (which references Col 27) works fine on its own and is based on the user typing a date into the cell, and the row moves over to the "Completed" worksheet within the workbook. For the 2nd statement, the user chooses an item from a Data Validation dropdown list (located in Col 28), then that row needs to move over to a sheet called "Inactive." It doesn't matter which item is chosen from the list - any choice needs to move it. It's not clear to me how to combine these statements.

It also crossed my mind that if the 2nd statement involves a dropdown list, if the structure can be based on <>"" or if I need to list out each choice available? I have tried for a couple of hours with different variations and can't get this combined statement to work. For the sake of sample code, let's say the dropdown choices are "No action taken", "In progress," and "pending." I've attached what the statement looks like right now.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow  As Long
Dim WkRg As Range
    If (Target.Column <> 27) Then Exit Sub
    If (Target.Value = "<>""") Then Exit Sub
    Set WkRg = Range(Cells(Target.Row, "A"), Cells(Target.Row, "AH"))
    Application.EnableEvents = False
    With Sheets("COMPLETED")
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row
        WkRg.Copy Destination:=.Cells(LastRow + 1, 1)
        WkRg.EntireRow.Delete
    Application.EnableEvents = True
    End With
    End Sub
       
    If (Target.Column <> 28) Then Exit Sub
    If (Target.Value - "<>""") Then Exit Sub
    Set WkRg = Range(Cells(Target.Row, "A"), Cells(Target.Row, "AH"))
    Application.EnableEvents = False
    With Sheets("INACTIVE")
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row
        WkRg.Copy Destination:=.Cells(LastRow + 1, 1)
        WkRg.EntireRow.Delete
    Application.EnableEvents = True
End With
End Sub

Thanks!

 

[NARAYANK991]

Hi ,

See if this works :

Private Sub Worksheet_Change(ByVal Target As Range)
            Dim LastRow  As Long
            Dim WkRg As Range
   
            If (Target.Column = 27) Then
              If (Target.Value = "<>""") Then Exit Sub
              Set WkRg = Range(Cells(Target.Row, "A"), Cells(Target.Row, "AH"))
   
              Application.EnableEvents = False
              With Sheets("COMPLETED")
                    LastRow = .Range("A" & Rows.Count).End(xlUp).Row
                    WkRg.Copy Destination:=.Cells(LastRow + 1, 1)
                    WkRg.EntireRow.Delete
              End With
              Application.EnableEvents = True
             
              Exit Sub
            Else
              If (Target.Column <> 28) Then Exit Sub
              If (Target.Value = "<>""") Then Exit Sub
   
              Set WkRg = Range(Cells(Target.Row, "A"), Cells(Target.Row, "AH"))
             
              Application.EnableEvents = False
              With Sheets("INACTIVE")
                    LastRow = .Range("A" & Rows.Count).End(xlUp).Row
                    WkRg.Copy Destination:=.Cells(LastRow + 1, 1)
                    WkRg.EntireRow.Delete
              End With
              Application.EnableEvents = True
             
            End If
End Sub

Narayan

 

[cmkarnes]

Hello Narayan - works perfectly!! As always, thank you for your extremely valuable assistance. I'd be lost without it. Take care.