Worksheet_change : entire column value deleted, identify non empty cells before this action

Question

I have a workbook with a main sheet for input and the values from the main sheet gets copied over to 2 sub sheets based on cell value of "type" column in main sheet.

Any value in the "comments" column in the sub sheets against these copied cells are added as comments to the main sheet's corresponding row.When the values in the "comments" column in the sub sheets are deleted at once, I want to identify the non empty cells before this action and delete the corresponding comments in main sheet.

Currently I have written code if a value is added/deleted in "comments" column in sub sheet which would then add/delete comments in main sheet's corresponding entry.

    Private Sub Worksheet_Change(ByVal Target As Range)

Dim temp As String
Dim tem As String
With Target
       If .Count = 1 And .Column = 8 And .Row < 600 Then
       tem = .Row
             If Sheets("Parts- input").Cells(tem, 8).Comment Is Nothing Then
                If Sheets("Pins").Cells(.Row, .Column).Value = "" Then
                   Sheets("Parts- input").Cells(tem, 8).Comment.Delete
              Else
               Sheets("Parts- input").Cells(tem, 8).AddComment "Lifts Sheet: " & Sheets("Pins").Cells(.Row, .Column).Value
               End If
             Else
              If Sheets("Pins").Cells(.Row, .Column).Value = "" Then
              Sheets("Parts- input").Cells(tem, 8).Comment.Delete
              Else
              Sheets("Parts- input").Cells(tem, 8).Comment.Text "Lifts Sheet: " & Sheets("Pins").Cells(.Row, .Column).Value
              End If
            End If
   End If
End With
End Sub

Answer 1

just playing with your code, i endet up with this:

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    If .Count = 1 And .Column = 8 And .row < 600 Then
      If Sheets("Pins").Cells(.row, .Column).Value = "" Then
        Sheets("Parts- input").Cells(.row, 8).Comment.Delete
      Else
        If Sheets("Parts- input").Cells(.row, 8).Comment Is Nothing Then
          Sheets("Parts- input").Cells(.row, 8).AddComment "Lifts Sheet: " & Sheets("Pins").Cells(.row, .Column).Value
        Else
          Sheets("Parts- input").Cells(.row, 8).Comment.Text "Lifts Sheet: " & Sheets("Pins").Cells(.row, .Column).Value
        End If
      End If
    Else
      If Not Intersect(Target, Target.Parent.Range("H1:H599")) Is Nothing Then
        Dim runner As Range, rng As Range
        For Each runner In Intersect(Target, Target.Parent.Range("H1:H599")).Cells
          If Sheets("Pins").Cells(runner.row, 8).Value = "" Then
            If rng Is Nothing Then
              Set rng = Sheets("Parts- input").Cells(runner.Rows, 8)
            Else
              Set rng = Union(rng, Sheets("Parts- input").Cells(runner.Rows, 8))
            End If
          End If
        End If
      Next
      rng.Comment.Delete
    End If
  End With
End Sub

you could delete them directly, but having a lot of cells, do it in one step will be faster :)

EDIT included Intersect to improve speed