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

  • Thread starter Thread starter Guy
  • Start date Start date
G

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
 
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
 
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
 
You actually do not have to select tempR

With tempR
'do some stuff
End With


Gord
 
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
 
Additional Info.
All cells in the range have some value and are not empty.
 
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
 
Thanks a bunch Gord, the 'VBA Macro code' works great. I'm learning. :)

Happy New Year,
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Excel - Select a value from one cell in Excel to update a SQL table 1
Selecting cells equals to a value 3
Pasting cell contents into a macro 1
Macro for Selecting Cells within a Range 1
Select multiple adjacent cells of multiple cells without selecting 7
MACRO RUN random percentage and copy of column values 1
Help on randomly selecting cells to SUM 2
Transfer cell values to another cell by selecting button. 2

Back
Top