How to merge two subs with Private Sub Worksheet_Change on sheet

Question

good morning I need to combine two Private Sub Worksheet_Change(ByVal Target As Range) I'm new to Excel VBA code, how can I do this? Code below.

1)

Option Explicit
Const strAFM As String = "D3:D1000"
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range, AFM As String, rngTomi As Range
    Set Rng = Range(strAFM)
    Set rngTomi = Intersect(Target, Rng)
    If rngTomi Is Nothing Then Exit Sub

    If rngTomi.Count <> 1 Then
        rngTomi.ClearContents
        Exit Sub
    End If
    If Trim(Target.Value) = "" Then Exit Sub

    AFM = Right("000000000" & Target.Value, 9)

    If isAFM(AFM) = False Then
        MsgBox "no afm"
        Target.Activate
        Exit Sub
    End If
End Sub

2)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub

    Dim Rng As Range
    Set Rng = Me.Range("ColTarget")

    If Intersect(Target, Rng) Is Nothing Then Exit Sub

    ResizeTbl
End Sub

Sorry, but you just posted a wall of code with a lot of IF statements. What is to merge? Where are the two procedures? — teylyn
Sorry for my mistake, I made the correction to my initial appointment. Thanks — yiannis

EvR EvR · Accepted Answer · 2019-04-18T09:03:00

Try:

Option Explicit
Const strAFM As String = "D3:D1000"
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range, AFM As String, rngTomi As Range

    If Not Target.Count > 1 Then
        Set Rng = Me.Range("ColTarget")
        If Not Intersect(Target, Rng) Is Nothing Then ResizeTbl
    End If

    Set Rng = Range(strAFM)
    Set rngTomi = Intersect(Target, Rng)
    If Not rngTomi Is Nothing Then
        If rngTomi.Count <> 1 Then
            Application.EnableEvents = False
                rngTomi.ClearContents
            Application.EnableEvents = False
            Exit Sub
        End If

    If Trim(Target.Value) = "" Then Exit Sub

    AFM = Right("000000000" & Target.Value, 9)

    If isAFM(AFM) = False Then
        MsgBox "no afm"
        Target.Activate
        Exit Sub
    End If
    End If

End Sub

How to merge two subs with Private Sub Worksheet_Change on sheet

1 Answers