Deselect a cell in a selected range using VBA

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126
Was hoping for something along theses lines, though the Cells.Deselct throws up a runtime error

Code:
For Each Cell In Selection
      If Cell.Font.ColorIndex = 5 Then
         Cell.Deselect
      End If
Next Cell
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
What version of Excel do you have? I have Excel 97, and deselect can only be used to change a selected chart to be a non-selected chart, and not be used on cell objects. Is that what causes the run-time error?
 
Upvote 0
Yeah It must be, the error states that deselct cannot be used on this type of object, any suggestions on how I can achieve this?
 
Upvote 0
Let's stand back a bit first ... why do you want to do this? There may be other ways of achieving your aim. ( also, if your aim is just to do this type of selection, there are free add-in tools to do this for you )
 
Upvote 0
OK, cheers Glenn, I have actually thought of another way around this problem, it isn't 100% automated but it is a LOT easier to program, the only problem this way is to add a cell to a multiple selection, do you know how to send ctrl and left mouse click from vba?
 
Upvote 0
Like this:

Code:
Sub Test()
    Dim Rng As Range
    Set Rng = Range("A1")
    Set Rng = Union(Rng, Range("A3"))
    MsgBox Rng.Address
End Sub
 
Upvote 0
Yp I'm trying the union approach but it's throwing up an error on rRange4, can u see why?...

Code:
 Set rRange2 = rRange1.SpecialCells(xlCellTypeConstants, 1)
             Set rRange3 = Cells(3, ValColLet)
              Set rRange4 = Union(rRange2, rRange3)
 
Upvote 0
They are on the same sheet

Code:
Private Sub SelectConstants()
 
    Dim rRange1 As Range
    Dim rRange2 As Range
    Dim rRange3 As Range
    Dim rRange4 As Range
    Dim valX As Characters
              
  'Select Constants (multiple selection)
  
  
    Set rRange1 = Range(Cells(4, Me.ValColLet.Value), Cells(65536, Me.ValColLet.Value).End(xlUp))
        On Error Resume Next
            
            Set rRange2 = rRange1.SpecialCells(xlCellTypeConstants, 1)
             Set rRange3 = Range(Cells(3, ValColLet))
              Set rRange4 = Union(rRange2, rRange3)
      
    On Error GoTo 0
 
    If rRange4 Is Nothing Then
       MsgBox "No Data Found", vbExclamation
            Unload Me
                Exit Sub
    End If
      
     rRange4.Select
 
         Call CopyAndPaste
 
             rRange4.ClearContents
  
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top