VBA Help simplifying lines of code

[Keojey]

Hello,

I'm trying make a simple macro that will clear the contents of certain cells in a row based on information in other cells.
So if C1 = 1 and F2 = 1, then I would have the contents of F2 be cleared. This repeats down columns all the way to C22 and F22.

I have a very messy way of doing this :

If Sheets("Sheet1").Range("F2").Text = Sheets("Sheet1").Range("C2").Text Then Sheets("Sheet1").Range("F2").ClearContents
If Sheets("Sheet1").Range("F3").Text = Sheets("Sheet1").Range("C3").Text Then Sheets("Sheet1").Range("F3").ClearContents
If Sheets("Sheet1").Range("F4").Text = Sheets("Sheet1").Range("C4").Text Then Sheets("Sheet1").Range("F4").ClearContents
If Sheets("Sheet1").Range("F5").Text = Sheets("Sheet1").Range("C5").Text Then Sheets("Sheet1").Range("F5").ClearContents
If Sheets("Sheet1").Range("F6").Text = Sheets("Sheet1").Range("C6").Text Then Sheets("Sheet1").Range("F6").ClearContents
If Sheets("Sheet1").Range("F7").Text = Sheets("Sheet1").Range("C7").Text Then Sheets("Sheet1").Range("F7").ClearContents
If Sheets("Sheet1").Range("F8").Text = Sheets("Sheet1").Range("C8").Text Then Sheets("Sheet1").Range("F8").ClearContents
If Sheets("Sheet1").Range("F9").Text = Sheets("Sheet1").Range("C9").Text Then Sheets("Sheet1").Range("F9").ClearContents
If Sheets("Sheet1").Range("F10").Text = Sheets("Sheet1").Range("C10").Text Then Sheets("Sheet1").Range("F10").ClearContents
If Sheets("Sheet1").Range("F11").Text = Sheets("Sheet1").Range("C11").Text Then Sheets("Sheet1").Range("F11").ClearContents
If Sheets("Sheet1").Range("F12").Text = Sheets("Sheet1").Range("C12").Text Then Sheets("Sheet1").Range("F12").ClearContents
If Sheets("Sheet1").Range("F13").Text = Sheets("Sheet1").Range("C13").Text Then Sheets("Sheet1").Range("F13").ClearContents
If Sheets("Sheet1").Range("F14").Text = Sheets("Sheet1").Range("C14").Text Then Sheets("Sheet1").Range("F14").ClearContents
If Sheets("Sheet1").Range("F15").Text = Sheets("Sheet1").Range("C15").Text Then Sheets("Sheet1").Range("F15").ClearContents
If Sheets("Sheet1").Range("F16").Text = Sheets("Sheet1").Range("C16").Text Then Sheets("Sheet1").Range("F16").ClearContents
If Sheets("Sheet1").Range("F17").Text = Sheets("Sheet1").Range("C17").Text Then Sheets("Sheet1").Range("F17").ClearContents
If Sheets("Sheet1").Range("F18").Text = Sheets("Sheet1").Range("C18").Text Then Sheets("Sheet1").Range("F18").ClearContents
If Sheets("Sheet1").Range("F19").Text = Sheets("Sheet1").Range("C19").Text Then Sheets("Sheet1").Range("F19").ClearContents
If Sheets("Sheet1").Range("F20").Text = Sheets("Sheet1").Range("C20").Text Then Sheets("Sheet1").Range("F20").ClearContents
If Sheets("Sheet1").Range("F21").Text = Sheets("Sheet1").Range("C21").Text Then Sheets("Sheet1").Range("F21").ClearContents
If Sheets("Sheet1").Range("F22").Text = Sheets("Sheet1").Range("C22").Text Then Sheets("Sheet1").Range("F22").ClearContents

Is there a way to make it more compressed so it could look more neat? The goal is to have it function the same exact way and to not clear the entire row when there is only one pair of repeating digits.

Thank you!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub ClearCels()
   Dim i As Long
   With Sheets("Sheet1")
      For i = 2 To 22
         If .Range("C" & i).Text = .Range("F" & i).Text Then .Range("F" & i).ClearContents
      Next i
   End With
End Sub

 

[Norie]

Try this.

Dim cl As Range

    For Each cl In Sheets("Sheet1").Range("F2:F22")
        If cl.Text = cl.Offset(, -3).Text Then cl.ClearContents
    Next cl

 

[Keojey]

Thank you both! Both ways work perfectly

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Rick Rothstein]

Give your ranges are fixed in size, this should also work...

Sub ClearCellsInColumnFifEqualToColumnC()
  [F2:F22] = Evaluate("IF(F2:F22=C2:C22,"""",F2:F22)")
End Sub

EDIT NOTE: The above works as long as Column F contains no blank cells with the range. If there could be blank cells in the range, use this instead...

Sub ClearCellsInColumnFifEqualToColumnC()
  [F2:F22] = Evaluate("IF(F2:F22=C2:C22,"""",IF(F2:F22="""","""",F2:F22))")
End Sub