(Excel) Deselect cell in selected range (how to?) 3

[krinid]

When I select a range and then hit CTRL and click a cell on a worksheet, my "Windows intuition" indicates that the cell should become unselected from the range. But this doesn't happen... it just becomes the active cell and remains part of the selected range.

Is there a way to unselect certain cells within a selected range?

 

[xlbo]

AFAIK - no. I have to say that my intuition works the other way round - If I have selected a range then any selection within that range would make the selection active

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[krinid]

Really? My intuition says that generally in Windows holding CTRL and clicking an item in a list selects/unselects selectively throughout a selection (excuse the overuse of 'select' words), so I expect this to carry over into Excel, although it unfortunately appears that it doesn't.

This would of course be invaluable when trying to, for example:
- select a large block of text minus 1 or 2 cells somewhere within
- selecting many various unconnected cells and accidentally clicking a wrong cell along the way

I've tried all combinations of CTRL, SHIFT, ALT, but no avail. It just strikes me as odd that this feature (CTRL + click) works this way in 95% of all Windows apps, but not Excel, where it would be most useful. In Excel CTRL + click only works when adding cells to a range, not removing them.

 

[xlbo]

I guess I'm just most used to excel so my "intuition" is based on how excel works rather than any other app

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[RamziSaab]

ok i have been doing research on this and with the help of google groups with is what i have found:

On your personal.xls (or if u want put it in the workbook ur working on) file on the workbook_open put this code:

**********************************************************
Private Sub Workbook_Open()
With Application.CommandBars("Cell&quot.Controls
With .Add(temporary:=True)
.Caption = "Unselect Active Cell"
.OnAction = ThisWorkbook.Name & "UnSelectActiveCell"
.BeginGroup = True
End With
With .Add(temporary:=True)
.Caption = "Unselect Active Area"
.OnAction = ThisWorkbook.Name & "!UnSelectActiveArea"
End With
End With


End Sub
*********************************************************

Then u put these in a module:

**************************************************
Sub UnSelectActiveCell()
Dim Rng As Range
Dim FullRange As Range

If Selection.Cells.Count > 1 Then
For Each Rng In Selection.Cells
If Rng.Address <> ActiveCell.Address Then
If FullRange Is Nothing Then
Set FullRange = Rng
Else
Set FullRange = Application.Union(FullRange, Rng)
End If
End If
Next Rng

If FullRange.Cells.Count > 0 Then
FullRange.Select
End If
End If

End Sub
Sub UnSelectActiveArea()

Dim Rng As Range
Dim FullRange As Range
Dim Ndx As Integer
If Selection.Areas.Count > 1 Then
For Each Rng In Selection.Areas
If Application.Intersect(ActiveCell, Rng) Is Nothing Then
If FullRange Is Nothing Then
Set FullRange = Rng
Else
Set FullRange = Application.Union(FullRange, Rng)
End If
End If
Next Rng
FullRange.Select
End If

End Sub
******************************************************

Right click on the area or activecell and u will see that u now have the option to unselect

Tell me if u need more help

 

[xlbo]

Ramzi - very nice - have a star - just a small point. Havn't tested in an active workbook but if you put the code in personal.xls, the line

.OnAction = ThisWorkbook.Name & &quot;UnSelectActiveCell&quot;
needs to be:
.OnAction = &quot;UnSelectActiveCell&quot;

and the same for the areas one as well

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[RamziSaab]

thanks for the star but real credit goes to Chip Pearson who wrote this in 1999!

 

[xlbo]

but you brought it to our (collective) attention ;-)

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[krinid]

RamziSaab,
If Chip Pearson were here, I'd give him a star, too! Simply by passing on this useful knowledge, you help anyone following this thread out, (in particular, Geoff & I have gained something from your help) and thus you earned a star from me, too. Thanks.

 

[RobTsintas]

I found this old solution to a query I had (it's astonishing that this is the only way to do such a simple thing!)

Anyway, I have added the code to my Personal.xls, and the new commands appear in the cell right-click menu.

However, they do not appear in the right-click menu if I am working in Page Break Preview mode.

Does anyone know if it's possible to get them to appear here? Is it a different CommandBars object?

Cheers