Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
How to Delete Contents of D2 if B2 = "SD"
How do I Delete Contents of D2 if B2 = "SD". Then continue through every row
in the file? |
#2
|
|||
|
|||
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 , "StarBoy2000" wrote: How do I Delete Contents of D2 if B2 = "SD". Then continue through every row in the file? |
#3
|
|||
|
|||
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 , "StarBoy2000" wrote: How do I Delete Contents of D2 if B2 = "SD". Then continue through every row in the file? |
#4
|
|||
|
|||
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 , "StarBoy2000" 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 |
#5
|
|||
|
|||
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 , "StarBoy2000" 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 |
#6
|
|||
|
|||
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" wrote: How do I Delete Contents of D2 if B2 = "SD". Then continue through every row in the file? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I conditionally delete rows based on cell contents? | Excel Worksheet Functions | |||
how to delete contents of cells having specific data | Excel Discussion (Misc queries) | |||
delete all the contents (sub folders and files) in the temp folder | Excel Discussion (Misc queries) | |||
Question from MOS file E03C-1-1 , Creating data and contents | Excel Discussion (Misc queries) | |||
Delete contents of unprotected cells in workbook | Excel Worksheet Functions |