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

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

"Guy" wrote:

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

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

On Wed, 31 Dec 2008 09:42:19 -0800, Guy
wrote:

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

"Guy" wrote:

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

You actually do not have to select tempR

With tempR
'do some stuff
End With


Gord

On Wed, 31 Dec 2008 12:07:10 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Wed, 31 Dec 2008 09:42:19 -0800, Guy
wrote:

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

"Guy" wrote:

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

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

"Gord Dibben" wrote:

You actually do not have to select tempR

With tempR
'do some stuff
End With


Gord

On Wed, 31 Dec 2008 12:07:10 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Wed, 31 Dec 2008 09:42:19 -0800, Guy
wrote:

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

"Guy" wrote:

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

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

"Guy" wrote:

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

"Gord Dibben" wrote:

You actually do not have to select tempR

With tempR
'do some stuff
End With


Gord

On Wed, 31 Dec 2008 12:07:10 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Wed, 31 Dec 2008 09:42:19 -0800, Guy
wrote:

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

"Guy" wrote:

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

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

On Thu, 1 Jan 2009 14:28:01 -0800, Guy
wrote:

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

"Gord Dibben" wrote:

You actually do not have to select tempR

With tempR
'do some stuff
End With


Gord

On Wed, 31 Dec 2008 12:07:10 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Wed, 31 Dec 2008 09:42:19 -0800, Guy
wrote:

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

"Guy" wrote:

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

That's OK


Gord

On Thu, 1 Jan 2009 14:31:00 -0800, Guy
wrote:

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

"Guy" wrote:

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

"Gord Dibben" wrote:

You actually do not have to select tempR

With tempR
'do some stuff
End With


Gord

On Wed, 31 Dec 2008 12:07:10 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Wed, 31 Dec 2008 09:42:19 -0800, Guy
wrote:

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

"Guy" wrote:

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

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

Happy New Year,
Guy

"Gord Dibben" wrote:

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

On Thu, 1 Jan 2009 14:28:01 -0800, Guy
wrote:

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

"Gord Dibben" wrote:

You actually do not have to select tempR

With tempR
'do some stuff
End With


Gord

On Wed, 31 Dec 2008 12:07:10 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Wed, 31 Dec 2008 09:42:19 -0800, Guy
wrote:

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

"Guy" wrote:

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

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

On Thu, 1 Jan 2009 19:15:01 -0800, Guy
wrote:

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

Happy New Year,
Guy

"Gord Dibben" wrote:

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

On Thu, 1 Jan 2009 14:28:01 -0800, Guy
wrote:

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

"Gord Dibben" wrote:

You actually do not have to select tempR

With tempR
'do some stuff
End With


Gord

On Wed, 31 Dec 2008 12:07:10 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Wed, 31 Dec 2008 09:42:19 -0800, Guy
wrote:

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

"Guy" wrote:

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