Clearing Blank Cells, or the appearance of Blank Cells.

Livin404

Active Member
Joined
Jan 7, 2019
Messages
352
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,470
Office Version
  1. 2013
Platform
  1. Windows
How about
VBA Code:
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

Active Member
Joined
Jan 7, 2019
Messages
352
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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,



Delete Roll Call and number.JPG
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,470
Office Version
  1. 2013
Platform
  1. Windows
So ....
VBA Code:
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
 
Solution

Livin404

Active Member
Joined
Jan 7, 2019
Messages
352
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Thank you, that is beautiful. Much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,523
Messages
5,572,639
Members
412,478
Latest member
MakeItWorkVBA
Top