Clearing cell contents based on another column's values

The Tamer

Well-known Member
Joined
Jun 10, 2004
Messages
520
Hi :)

I need code to do this:

Look at F4:F79, find and all zeros, leaving blank cells in their place. And where a zero is removed in Column F, the text in the corresponding cell in column E should then be cleared too.

Any suggestions please? :biggrin:

Thanks a million.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
Hi Tamer

Would this do?
Code:
Sub ClearText()
Dim myRng As Range
Dim cell As Range

Set myRng = Range("F4:F79")

For Each cell In myRng
    If cell.Value = "0" Then
        cell.ClearContents
        cell.Offset(0, -1).ClearContents
    End If
Next cell
End Sub
HTH

Regards
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi,

Right click on the sheet tab that you want to run this code then clilck
View Code, then paste the code below
Code:
Public z As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If z <> 0 Then Exit Sub
     If Intersect(Target, Me.Range("f4:f79")) Is Nothing Then Exit Sub
        Application.EnableEvents = False
            If IsEmpty(Target) Then
                Target.Offset(, -1).ClearContents
            End If
        Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Me.Range("f4:f79")) Is Nothing Then Exit Sub
        If Target.Count <> 1 Then Exit Sub
            z = Target.Value
End Sub

hope this helps

jindon
 

The Tamer

Well-known Member
Joined
Jun 10, 2004
Messages
520
Hey both, thanks.

I always feel awful when two or more people have provided different answers and I can employ only one!

Both work well, and so you've both contributed to solving my problem.

So thanks

(but i used Glaswegian's) :)
 

Forum statistics

Threads
1,147,844
Messages
5,743,510
Members
423,800
Latest member
IuneKeiki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top