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.

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.

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.