Combine Private Sub Worksheet_Change VBA Code on same worksheet

Please help me combine the codes below so that they work in the same worksheet.
The work if only one set is applied but not together.

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cL
        If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
        For Each cL In Target.Cells
            Application.EnableEvents = False
            On Error GoTo enable
            If Len(cL) > 40 Then
                MsgBox "More than 40 characters input into cell " & Replace(cL.Address, "$", "") & vbCrLf & "Data will be Truncated", vbExclamation + vbOKOnly
                cL.Value = Strings.Left(cL, 40)
                Else
            End If
        Next cL
enable:
    Application.EnableEvents = True

End Sub

  
Private Sub Worksheet_Change(ByVal Target As Range)
   
   Dim FirstRow As Long
   Dim i As Long
   Dim LastRow As Long
   Dim Rng As Range

        FirstRow = 2
        LastRow = Range("B:B,A:A").Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False, False, False).Row
        Set Rng = Range(Cells(FirstRow, "B"), Cells(LastRow, "A"))
        
        If Intersect(Target, Rng) Is Nothing Then Exit Sub
        
            For i = FirstRow To LastRow
                If Cells(i, "B") = "" And Cells(i, "A") <> "" Then
                    MsgBox "Channel ID is Missing" & vbCrLf & "Add Channel ID and Product Code together" & vbCrLf & "Data will be cleared", vbExclamation + vbOKOnly
                    Application.EnableEvents = False
                        Rng.Columns(1).Cells.ClearContents
                        Rng.Columns(2).Cells.ClearContents
                    Application.EnableEvents = True
                    Exit For
                End If
            Next i
    
End Sub

Hi, stanlelma,

please add code-tags around the procedures as required per Forum Rule #3.

Ciao,
Holger

Hi, stanlelma,

maybe like this (untested)

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim cL As Range
  Dim FirstRow As Long
  Dim i As Long
  Dim LastRow As Long
  Dim Rng As Range
  If Not Intersect(Target, Range("I:I")) Is Nothing Then
    For Each cL In Target.Cells
        Application.EnableEvents = False
        On Error GoTo enable
        If Len(cL) > 40 Then
            MsgBox "More than 40 characters input into cell " & Replace(cL.Address, "$", "") & vbCrLf & "Data will be Truncated", vbExclamation + vbOKOnly
            cL.Value = Strings.Left(cL, 40)
        End If
    Next cL
  Else
    FirstRow = 2
    LastRow = Range("B:B,A:A").Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False, False, False).Row
    Set Rng = Range(Cells(FirstRow, "B"), Cells(LastRow, "A"))
    If Not Intersect(Target, Rng) Is Nothing Then
      For i = FirstRow To LastRow
          If Cells(i, "B") = "" And Cells(i, "A") <> "" Then
              MsgBox "Channel ID is Missing" & vbCrLf & "Add Channel ID and Product Code together" & vbCrLf & "Data will be cleared", vbExclamation + vbOKOnly
              Application.EnableEvents = False
                  Rng.Columns(1).Cells.ClearContents
                  Rng.Columns(2).Cells.ClearContents
              Application.EnableEvents = True
              Exit For
          End If
      Next i
    End If
  End If

enable:
  Application.EnableEvents = True

End Sub

although I would recommedn to use SpecialCells(XlCellTypeBlanks) or WorksheetFunction.CountBlank(Rrng) which would only include cells that really are blank (no formulas) instead of looping through all the range.

Ciao,
Holger