Sort Macro

[Sue]

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

 

[joel]

why are your sorting?

Sub Macro12()
set c = range("F6:F15").find(what:=F5, _
lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
Range("A6:F15").clearcontenets
end if
end Sub

 

[Dave Peterson]

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

 

[Sheeloo]

"nothing in the range F6:F15 does not equal the value of F5 " is same as "at
least one value in F6:F15 does not equal the value in F5", right?

If yes, then use the following macro...
btw why do you want to sort to do this?

Also this is the first time I have seen a sheet named like 'OB;In;Av' (hope
you did not mean to select three sheets by that)

Sub Macro12()
Sheets("OB;In;Av").Select
flag = False
For i = 6 To 15
If Cells(5, 6) <> Cells(i, 6) Then
'MsgBox Cells(5, 6)
flag = True
Exit For
End If
Next
If flag Then Range("A6:F15").ClearContents
End Sub

 

[Sue]

Hi

To the first question I'm sorting because the numbers in F5:F15 are all
different from another sheet and the highest number in the series has to go
into into F5

the sheet is a single sheet and named that way because it works out an
Average therefore a glance at the tab shows me I'm on the right sheet.

 

[Sue]

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

 

[Dave Peterson]

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

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

 

[Sue]

Hi Dave

Eventually got everything working OK
Sorry about not explaining properly at the outset