Delete/clear a cell based on another cells contents

[Guest]

Could someone assist me with the following problem please.

I have a sheet that has about 1000 rows and about 50 columns. I would like
to write a macro that will clear the cell contents of 4 cells in one row IE
AC3,AD3,AE3 and BL3 based on whether the contents of cell BW3 = "Yes". The
macro will the drop to the next cell IE BW4 and will clear the contents of
the corresponding cells AC4,AD4,AE4 and BL4 if this has a "YES" in it. This
will then continue until the bottom of the sheet.

 

[Guest]

Here you go...

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

Set wks = ActiveSheet
Set rngToSearch = wks.Range("BW3:BW65535")
Set rngCurrent = rngToSearch.Find("Yes", , , xlWhole)
If Not rngCurrent Is Nothing Then
Set rngFirst = rngCurrent
Do
rngCurrent.Offset(0, -11).ClearContents
rngCurrent.Offset(0, -44).ClearContents
rngCurrent.Offset(0, -45).ClearContents
rngCurrent.Offset(0, -46).ClearContents

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

HTH

Jim Thomlinson

 

[Chandra]

Hi,
Try to use the formula in the cells where u wanth the logic to be
implemented

IF(<Column>='YES',value_if_true,value_if_false)



best Regards,
Chandra
http://chanduas.blogspot.com/

 

[Guest]

Thanks alot.

Here you go...

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

Set wks = ActiveSheet
Set rngToSearch = wks.Range("BW3:BW65535")
Set rngCurrent = rngToSearch.Find("Yes", , , xlWhole)
If Not rngCurrent Is Nothing Then
Set rngFirst = rngCurrent
Do
rngCurrent.Offset(0, -11).ClearContents
rngCurrent.Offset(0, -44).ClearContents
rngCurrent.Offset(0, -45).ClearContents
rngCurrent.Offset(0, -46).ClearContents

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

HTH

Jim Thomlinson