Any way to deselect a select in VBA?

  • I have this code in my file.

    Code
    1. Worksheets.Select
    2. Cells.Select
    3. Selection.Copy
    4. Selection.PasteSpecial Paste:=xlPasteValues
    5. ActiveSheet.Select
    6. Application.CutCopyMode = False


    It selects everything in the sheet and copies it and then pastes back to values only.


    Is there any way to deselect everything when its done?

  • Re: Any way to deselect a select in VBA?


    Hi there,


    There are 2 simple ways to achieve this, first, don't select the sheet to start with

    Code
    1. ThisWorkbook.Sheets("Sheet1").UsedRange.Value = ThisWorkbook.Sheets("Sheet1").UsedRange.Value


    or, just select another range when done

    Code
    1. ThisWorkbook.Sheets("Sheet1").Range("A1").select
  • Re: Any way to deselect a select in VBA?



    This is what I am using. All cells on each are selected when I this is finished running. There is not any problem just a nuisance to me.


    I just wanted to unselect everything when it is thru.

  • Re: Any way to deselect a select in VBA?


    I put your code in as below but it didn't change anything as far as the selection goes.


  • Re: Any way to deselect a select in VBA?


    Actually I can't do that because it has our cost data and prices and such on it. However it wouldn't do you much good because all of the data is pulled in from other files. I used the addin Morefunc so I can access data from files that are closed. This is a formula in one cell.


    Code
    1. {=IF(ROWS(A$6:A6)>'C:\Work\Plants\Cary\Plant Costs\[Aggregate Usage Week Ending 4-11-15.xlsm]Monday'!$H$6,"",INDEX('C:\Work\Plants\Cary\Plant Costs\[Aggregate Usage Week Ending 4-11-15.xlsm]Monday'!$H$9:$H$15,SMALL(IF(FREQUENCY(IF('C:\Work\Plants\Cary\Plant Costs\[Aggregate Usage Week Ending 4-11-15.xlsm]Monday'!$H$9:$H$15<>"",MATCH('C:\Work\Plants\Cary\Plant Costs\[Aggregate Usage Week Ending 4-11-15.xlsm]Monday'!$H$9:$H$15,'C:\Work\Plants\Cary\Plant Costs\[Aggregate Usage Week Ending 4-11-15.xlsm]Monday'!$H$9:$H$15,0)),ROW('C:\Work\Plants\Cary\Plant Costs\[Aggregate Usage Week Ending 4-11-15.xlsm]Monday'!$H$9:$H$15)-ROW('C:\Work\Plants\Cary\Plant Costs\[Aggregate Usage Week Ending 4-11-15.xlsm]Monday'!$H$9)+1),ROW('C:\Work\Plants\Cary\Plant Costs\[Aggregate Usage Week Ending 4-11-15.xlsm]Monday'!$H$9:$H$15)-ROW('C:\Work\Plants\Cary\Plant Costs\[Aggregate Usage Week Ending 4-11-15.xlsm]Monday'!$H$9)+1),ROWS(A$6:A6))))}
  • Re: Any way to deselect a select in VBA?


    locking cells doesn't take effect until the worksheet is protected so that wouldn't make any difference.


    Can you not upload the file and just remove and personal/identifiable data from it? The whole point of OzGrid is to provide free help to people that want to learn and sending things privately defeats that object - more importantly there are a vast amount of professionals and experts on here that may come across this and be able to provide a better solution so sending things privately means you're missing out on all that potential.


    Alternatively you could set up a small sample to show what you're trying to achieve?