Clearing Blank Cells, or the appearance of Blank Cells.

[Livin404]

Greetings, I am trying to insert text (Column E) in a cell if there is a number in another cell Column E. The problem is even for the blank cells "TBD" is being inserted in Column D. Now I'm trying to clear the contents of Column D, so excel doesn't think there is data there. I would need to except for this code to ignore one is the word "ROLL CALL" and another is any four digit number "####". Column D is in a Text format.

Sub DoNotContainrollcall()

Dim rng As Range
Dim cell As Range
Dim ContainWord As String

With Sheets("72 HR")
      With .Range("D2", .Range("D" & Rows.Count).End(xlUp))

  ContainWord = "ROLL CALL". "####"

  For Each cell In rng.Cells
    If cell.Find(ContainWord) Is Nothing Then cell.Clear
  Next cell
End With
End With
End Sub

 

[Michael M]

How about

Sub MM1()
Dim cell As Range
  For Each cell In Sheets("72 HR").Range("D2", Range("D" & Rows.Count).End(xlUp))
    If cell.Value = "ROLL CALL" Or IsNumeric(cell) And Len(cell) = 4 Then
        cell.Clear
    End If
  Next cell
End Sub

 

[Livin404]

Thank you I think we are heading in the right directions. As you see in the second screen shot to the right in cleared everything out of Column D. I need Roll Call and the four digit number to remain. When this is done, I have another macro when activated will place a TBD in Column E on the same row as the time is in Column D. For some reason the blank appearing spaces in Column D, Excel thinks there is data in there. If there is a blank space in Column D then in Column E on the same row will also be blank. Thank you,

 

[Michael M]

So ....

Sub MM1()
Dim cell As Range
  For Each cell In Sheets("72 HR").Range("D2", Range("D" & Rows.Count).End(xlUp))
    If cell.Value <> "ROLL CALL" And Len(cell) <> 4 Then
        cell.Clear
    End If
  Next cell
End Sub

 

[Livin404]

Thank you, that is beautiful. Much appreciated.