Hi Dave
If your sub only fires when data is entered into H10 then surely you will
only get an address formed on your second worksheet when there is a client
representative, when there is no data in H10 the sub wont fire at all so
there will be no address created for those without a representative!
You could put it in as a worksheet change so when the user selects the
conditional letter worksheet to view the addresses the sub will fire to
update them!
You can easily remove the extra data by adding = "" for the extra cells

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Set D = Worksheets("conditional letter")
Set E = Worksheets("Profiles Research Check List")

If Range("H10").Value = "" Then
D.Range("B13") = E.Range("H4")
D.Range("B14") = E.Range("H9")
D.Range("B15") = E.Range("H8") & ","
D.Range("C15") = E.Range("I8") & ","
D.Range("D15") = E.Range("J8")
D.Range("C16") = ""
D.Range("D16") = ""
Else
D.Range("B13") = E.Range("H9")
D.Range("B14") = "" & E.Range("H4")
D.Range("B15") = E.Range("H10")
D.Range("C15") =""
D.Range("B16") = E.Range("H11") & ","
D.Range("C16") = E.Range("I11") & ","
D.Range("D16") = E.Range("J11")
End If
End Sub