Macro to select cells without a certain value and select a menu it

[Guy]

I need a way to auto select all cells within a particular range that do not
have a certain value, (Bye), and if possible to also select a custom menu
function. The menu function is a 'random sort' feature that I have on my menu
bar and I wish to select a certain feature under the dropdown menu (random
sort selected cells) with this macro. I would like to have a macro to do both
with one click but just the macro to auto select all cells within the range
without value (Bye) will do.

Thanks,
Guy

 

[Guy]

I have been trying to get a "IF" macro to work for this but am having trouble
figuring out how to select just the cells without value "Bye". Can someone
please help.

Thanks,
Guy

 

[Gord Dibben]

Sub Find_Cells()

Dim Cell As Range, tempR As Range
For Each Cell In ActiveSheet.UsedRange
'edit the range to suit or just Selection
If Cell.Value <> "Bye" Then
If tempR Is Nothing Then
Set tempR = Cell
Else
Set tempR = Union(tempR, Cell)
End If
End If
Next Cell
If tempR Is Nothing Then
MsgBox "There are no cells " & _
"in the selected range."
End
End If

tempR.Select
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

You actually do not have to select tempR

With tempR
'do some stuff
End With


Gord

 

[Guy]

Hi Gord,
I am just a novice at this and can't figure out how the formula works.
It selects all cells on the entire sheet that do not have "Bye" and I can't
figure out how to get it to just select all cells in a range (E2 thru E17 for
this one) that do not have "Bye".

Happy New Year!
Guy

 

[Guy]

Additional Info.
All cells in the range have some value and are not empty.

 

[Gord Dibben]

Change this line...............For Each Cell In ActiveSheet.UsedRange

to this..............For Each Cell In ActiveSheet.Range("E2:E17")

BTW this is not a formula. It is VBA macro code.


Gord

 

[Gord Dibben]

That's OK


Gord

Additional Info.
All cells in the range have some value and are not empty.

 

[Guy]

Thanks a bunch Gord, the 'VBA Macro code' works great. I'm learning.

Happy New Year,
Guy

 

[Gord Dibben]

Good to hear.

I'm just learning too.

The original code came from Bob Flanagan for highlighting locked or unlocked
cells on a sheet.

I just modified for your case.


Gord