Clearing cells based on other cell values

[sschluet]

I have the following code that trolls through 3 columns with some blank spaces in them and it gives me a list of values in I2:I5 that I'm looking for but the red highlighted part isn't working:

Sub common2()
Dim sh As Worksheet, lr As Long, rng As Range, Brng As Range, Crng As Range
Set sh = Sheets(6) 'Edit sheet name

If sh.Range("C1") Is Nothing Then
Range("I2:I5").ClearContents
Else

lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A1:A" & lr)
For Each C In rng
Set Brng = sh.Range("B:B").Find(C.Value, LookIn:=xlValues)
Set Crng = sh.Range("C:C").Find(C.Value, LookIn:=xlValues)
If Not Brng Is Nothing And Not Crng Is Nothing Then
sh.Cells(Rows.Count, 9).End(xlUp)(2) = C.Value
End If
Set Brng = Nothing
Set Crng = Nothing
Next

End If

End Sub

When I run this if there are previous values in I column then it writes the new values below the first rather than clearing them, which is not what I'm looking for.

The 3 columns that have data in them get it from VLOOKUP because I'm very new to VBA and don't really know what I'm doing and I have a feeling that they are not returning "Nothing" to the code so that's why it's not working:

=IFERROR(IF(VLOOKUP(Searching_problem_AND_equip!$B$15,Source!$X$3:$AG$13,2,FALSE)=0,"",VLOOKUP(Searching_problem_AND_equip!$B$15,Source!$X$3:$AG$13,2,FALSE)),"")

Could someone help me get this piece working so my answers don't repeat?

 

[Jonmo1]

This is simply testing if the reference is a Valid Range..Not if the range is empty..
If sh.Range("C1") Is Nothing Then

instead, try
If sh.Range("C1").Value = "" Then

 

[sschluet]

Nope, that doesn't seem to do anything either. Any other ideas?

 

[Jonmo1]

Oh wait, you need the sh reference on both lines

If sh.Range("C1").Value = "" Then
sh.Range("I2:I5").ClearContents

 

[sschluet]

Great! It works! Thanks for your help.