Delete/clear a cell based on another cells contents
 

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.

Delete/clear a cell based on another cells contents
 

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


"jademaddy" wrote:

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.

Delete/clear a cell based on another cells contents
 

Thanks alot.

"Jim Thomlinson" wrote:

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


"jademaddy" wrote:

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.