I wasn't close!

Dim myCell as Range
dim myRng as range
with worksheets("OB;In;Av")
set myrng = .range("f6:f15")
for each mycell in myrng.cells
if mycell.value = .range("F5").value then
'keep it
else
mycell.clearcontents
end if
next mycell
end with

If upper/lowercase don't matter:

if mycell.value = .range("F5").value then
becomes
if lcase(mycell.value) = lcase(.range("F5").value) then

Sue wrote:

Hi Dave
I added your code onto the end of the sort macro and it cleared all the
contents of A6:F15 just leaving the value of F5 I suppose what I really
wanted was anything in F6:F15 that was equal to F5 should remain only
anything in F6:F15 that didn't equal F5 should have the contents cleared --
does that make sense???
I used the code below from your post.

with worksheets("OB;In;Av")
if application.countif(.range("f6:f15"), .range("F5").value) = 0 then
.range("A6:F15").clearcontents
end if
end with

--
Many Thanks

Sue

"Dave Peterson" wrote:

Do you want to clear A6:F15 if there is no match in F6:F15 for the value in F5?

If yes, another way:

with worksheets("OB;In;Av")
if application.countif(.range("f6:f15"), .range("F5").value) = 0 then
.range("A6:F15").clearcontents
end if
end with

Or do you want to clear A6:F15 if all the cells in F6:F15 match F5?

with worksheets("OB;In;Av")
if application.countif(.range("f6:f15"), .range("F5").value) _
= .range("f6:F15").cells.count then
.range("A6:F15").clearcontents
end if
end with


Sue wrote:

Hi
Can you help with my sort macro below,

If nothing in the range F6:F15 does not equal the value of F5 then clear the
contents of A6:F15

Sub Macro12()
Sheets("OB;In;Av").Select
Range("A5:F15").Select
Selection.Sort Key1:=Range("F5"), Order1:=xlDescending,
Key2:=Range("E5") _
, Order2:=xlDescending, Header:=xlNo, OrderCustom:=1,
MatchCase:=False End Sub

--
Many Thanks

Sue

--

Dave Peterson


--

Dave Peterson