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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,540
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?
 

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126
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?
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,540
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 )
 

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126

ADVERTISEMENT

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?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Like this:

Code:
Sub Test()
    Dim Rng As Range
    Set Rng = Range("A1")
    Set Rng = Union(Rng, Range("A3"))
    MsgBox Rng.Address
End Sub
 

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126

ADVERTISEMENT

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)
 

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126
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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What error do you get and where? I'm surprised you get one at all given On Error Resume Next.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,376
Messages
5,601,260
Members
414,439
Latest member
norideen

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
Top