Results 1 to 4 of 4

VBA to unselect a selected range

This is a discussion on VBA to unselect a selected range within the Excel Questions forums, part of the Question Forums category; Hi all, This is probably a very simple question, but I just can't seem to get it. How do I ...

  1. #1
    Board Regular
    Join Date
    Jul 2002
    Posts
    95

    Default

    Hi all,

    This is probably a very simple question, but I just can't seem to get it.

    How do I unselect a range within a macro?

    The macro takes a range, manipulates it, but then I can't get it to unselect before moving on (there is a loop and if not specifically told to unselect, I will loose data).

    apparently, Selection.Unselect does not work, nor does MyRange.Unselect


    Thanks in advance

    J

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339

    Default

    Just select another range or cell.

    Just as an aside, it's rare to need to select anything when using VBA. As an example, the following give identical results when copying and pasting on the active sheet-
       
    Range("A1").Select
    Selection.Copy
    Range("B1").Select
    ActiveSheet.Paste

    Range("A1").Copy Range("B1")



  3. #3
    Board Regular
    Join Date
    Jul 2002
    Posts
    95

    Default

    I don't think I can do that. The reason being I am using a union command and if there are cell(s) selected other than only the ones I want, they will be thrown into the mix a second time giving me erroneous results.

    Here is my entire code:

    Sub Select_Values_In_Range()

    Dim MyRange As Range
    Dim Message, val As Double
    Dim MyRange2 As Range
    Dim ave As Double
    On Error GoTo 10
    Message = "Enter cut-off value for screening data"
    val = InputBox(Message)
    5
    Set MyRange = Application.InputBox(prompt:="Select range", Type:=8)
    Dim c As Range
    Dim Rng As Range
    For Each c In MyRange
    If c.Value > val Then
    If Rng Is Nothing Then
    Set Rng = c
    Else
    Set Rng = Union(Rng, c)
    End If
    End If
    Next c
    Rng.Select
    Selection.Clear
    ave = Application.WorksheetFunction.Average(MyRange)
    Set MyRange2 = MyRange.SpecialCells(xlCellTypeBlanks)
    MyRange2.Select
    Selection.Activate
    Selection.Font.ColorIndex = 3
    Selection = ave
    ' RIGHT HERE is where I need to unselect MyRange2 or when it loops I will loose the data

    GoTo 5
    10
    End Sub


    Thanks again

  4. #4
    Board Regular
    Join Date
    Jul 2002
    Posts
    95

    Default

    I fixed it using this line:

    Set Rng = Nothing


    thanks again

    J

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com