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

[StarBoy2000]

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




In article <B15325F3-B1D4-48C9-B9C2-98C3E7F40CAF@microsoft.com>,
"StarBoy2000" <StarBoy2000@discussions.microsoft.com> wrote:

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

[StarBoy2000]

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" wrote:

> 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
>
>
>
>
> In article <B15325F3-B1D4-48C9-B9C2-98C3E7F40CAF@microsoft.com>,
> "StarBoy2000" <StarBoy2000@discussions.microsoft.com> wrote:
>
> > How do I Delete Contents of D2 if B2 = "SD". Then continue through every row
> > in the file?
>

[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




In article <96455DE5-CAD1-4635-9021-8C1779ECBB0C@microsoft.com>,
"StarBoy2000" <StarBoy2000@discussions.microsoft.com> wrote:

> 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

[StarBoy2000]

Great, that did it... Thanks a lot

"JE McGimpsey" wrote:

> 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
>
>
>
>
> In article <96455DE5-CAD1-4635-9021-8C1779ECBB0C@microsoft.com>,
> "StarBoy2000" <StarBoy2000@discussions.microsoft.com> wrote:
>
> > 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
>

[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


On Thu, 21 Jul 2005 07:47:09 -0700, "StarBoy2000"
<StarBoy2000@discussions.microsoft.com> wrote:

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