How to Delete Contents of D2 if B2 = "SD"

[Guest]

How do I Delete Contents of D2 if B2 = "SD". Then continue through every row
in the file?

 

[JE McGimpsey]

You can't delete the contents using worksheet functions, but if D2
contains a formula, you can make the cell appear blank:

D2: =IF(B2="SD","",<your formula here>)

If D2 contains a constant/user entered value, or if you really want it
blank, then you'll need to use an Event macro.

If B2 will contain a user entry, put this in the worksheet code module
(right-click the worksheet tab and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Address(False, False) = "B2" Then _
If .Value = "SD" Then _
Range("D2").ClearContents
End With
End Sub

or, if B2 has a formula instead, put this in the worksheet code module
instead:

Private Sub Worksheet_Calculate()
If Range("B2").Value = "SD" Then _
Range("D2").ClearContents
End Sub

 

[Guest]

I've been looking around at other solutions and found the following that
works. But I can only get it to work on one row. I need it to loop thru
every row in column B, check for "SD" and clear the contents of the same row
in column D. Can you help me with that piece?

Public Sub ClearColumnContents()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFirst As Range
Dim rngCurrent As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Range("B2")
Set rngCurrent = rngToSearch.Find("SD", , , xlWhole)
If Not rngCurrent Is Nothing Then
Set rngFirst = rngCurrent
Do
rngCurrent.Offset(0, 2).ClearContents

Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
End If
End Sub

 

[JE McGimpsey]

One way, with a few minor modifications:

Public Sub ClearColumnContents()
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim strFirstAddress As String

With ActiveSheet
Set rngToSearch = .Range("B2:B" & _
.Range("B" & .Rows.Count).End(xlUp).Row)
End With
Set rngCurrent = rngToSearch.Find( _
What:="SD", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rngCurrent Is Nothing Then
strFirstAddress = rngCurrent.Address
Do
rngCurrent.Offset(0, 2).ClearContents
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = strFirstAddress
End If
End Sub

 

[Guest]

Great, that did it... Thanks a lot

One way, with a few minor modifications:

Public Sub ClearColumnContents()
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim strFirstAddress As String

With ActiveSheet
Set rngToSearch = .Range("B2:B" & _
.Range("B" & .Rows.Count).End(xlUp).Row)
End With
Set rngCurrent = rngToSearch.Find( _
What:="SD", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rngCurrent Is Nothing Then
strFirstAddress = rngCurrent.Address
Do
rngCurrent.Offset(0, 2).ClearContents
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = strFirstAddress
End If
End Sub

 

[Gord Dibben]

Starboy

Only through the use of VBA code.

Option Compare Text
Sub Delete_Stuff()
'using set column
Dim RngCol As Range
Dim i As Range
Set RngCol = Range("B1", Range("B" & Rows.Count). _
End(xlUp).Address)
For Each i In RngCol
If i.Value = "SD" Then _
i.Offset(0, 2).ClearContents
Next i
End Sub


Gord Dibben Excel MVP