Excel VBA: how to clear a selection without activating the sheet

20
2014-04
  • Nick

    Edit:

    Not to clear the data in the selection, rather to remove the selection itself. I suppose this would be equivalent to selecting a different range, such as A1..

  • Answers
  • caliban

    erm, you can always use VBA to reference the worksheet and cell range?

    Option Explicit
    
    Sub RemoveValues()
      Worksheets("ACME_Sales").Range("A1:B17").ClearContents
    End Sub
    

    This will clear off cells A1 to B17 in the worksheet ACME_Sales.

    EDITED Edit answer to reflect edited question.

    Sub SelectNewCell()
      Worksheets("ACME_Sales").Select
      Range("E2:E2").Select
    End Sub
    

    This will let VBA remove any selection on ACME_Sales and then just select the cell E2.


  • Related Question

    microsoft excel - How do I create a VBA macro that will copy data from an entry sheet, into a summary sheet by date
  • Mukkman

    I'm trying to create a macro that will copy data from a data entry sheet into a summary sheet. The entry sheet is going to be cleared daily so I can't use a formula just to reference it. I want the user to be able to enter a date, run a macro, and have the macro copy the data from the entry sheet into the cells for the corresponding date on the summary sheet. I've looked around and found bits and pieces of how to do this but I can't put it all together.

    Update:

    Thanks to the information below I was able to find some additional data. I have a pretty crude macro that works if the user manually selects the correct cell. Now I just need to figure out how to automatically select the current cell relative to the current date.

    Sub Update_Deposits()  
    '  
    ' Update_Deposits Macro  
    '  
    
    Dim selectedDate As String  
    Dim rangeFound As Range  
    selectedDate = Sheets("Summary Sheet").Range("F3")  
    Set rangeFound = Sheets("Deposits").Cells.Find(CDate(selectedDate))  
    
    
    Dim Total1 As Double  
    Dim Total2 As Double  
    Dim Total3 As Double  
    Dim Total4 As Double  
    Dim Total5 As Double  
    
      Total1 = Sheets("Summary Sheet").Range("E6")  
      Total2 = Sheets("Summary Sheet").Range("E7")  
      Total3 = Sheets("Summary Sheet").Range("E8")  
      Total4 = Sheets("Summary Sheet").Range("E9")  
      Total5 = Sheets("Summary Sheet").Range("E10")  
    
    If Not (rangeFound Is Nothing) Then  
        rangeFound.Offset(0, 2) = Total1  
        rangeFound.Offset(0, 3) = Total2  
        rangeFound.Offset(0, 4) = Total3  
        rangeFound.Offset(0, 6) = Total4  
        rangeFound.Offset(0, 7) = Total5  
    End If  
    
    '  
    End Sub  
    

    This version will find the first value on the page and fill in values:

    Sub Update_Deposits()  
    '  
    ' Update_Deposits Macro  
    '  
    
    Dim selectedDate As String  
    Dim rangeFound As Range  
    selectedDate = Sheets("Summary Sheet").Range("F3")  
    Set rangeFound = Sheets("Deposits").Cells.Find(CDate(selectedDate))  
    
    Dim Total1 As Double  
    Dim Total2 As Double  
    Dim Total3 As Double  
    Dim Total4 As Double  
    Dim Total5 As Double  
    
      Total1 = Sheets("Summary Sheet").Range("E6")  
      Total2 = Sheets("Summary Sheet").Range("E7")  
      Total3 = Sheets("Summary Sheet").Range("E8")  
      Total4 = Sheets("Summary Sheet").Range("E9")  
      Total5 = Sheets("Summary Sheet").Range("E10")  
    
    If Not (rangeFound Is Nothing) Then  
        rangeFound.Offset(0, 2) = Total1  
        rangeFound.Offset(0, 3) = Total2  
        rangeFound.Offset(0, 4) = Total3  
        rangeFound.Offset(0, 6) = Total4  
        rangeFound.Offset(0, 7) = Total5  
    End If  
    
    '  
    End Sub  
    

  • Related Answers
  • Geoff

    Since you're starting with a macro solution, I'm assuming you have enough programming knowledge to put the final result together; so given that, the main pieces I think you need are:

    Get started

    Make sure the developer ribbon is showing, then switch to the VBA editor (Alt-F11).
    Read through this page to get an appreciation of the overall process; some of the examples may be very close to what you want (e.g. 'Automatically Creating Workbook Files from Worksheet Data').

    Reading a cell

    dim myVar1 as string
    myVar1 = Sheets("Sheet1").Range("A1")
    

    That will allow to read from any particular cell on any particular sheet.

    Aggregating cell values

    Dim myvar2 As Integer
    myvar2 = Application.WorksheetFunction.Sum(Sheets("Sheet1").Range("B4:B6"))
    

    That will allow you to use any of the standard worksheet functions, on any range of cells.

    Writing to a cell

    Sheets("Sheet2").Range("A1") = myVar1
    

    (simply the reverse of reading)

    Connecting the macro to a button on a sheet

    After you create your macro, add a button to your master sheet wherever makes the most sense, following this guide.

    Good luck!

    EDIT 1:

    Writing based on offset

    To write to a particular cell based on a date offset, you could use the DateDiff function to get the distance to offset, e.g.:

    Dim startDate As Date
    Dim currDate as Date
    Dim dateOffset As Integer
    
    startDate = #10/21/2011#
    currDate = Sheets("Summary Sheet").Range("F3")
    dateOffset = DateDiff("d", startDate, currDate)
    

    Then, you can write to a particular cell in the same fashion you already are:

    ActiveSheet.Range("A1").Offset(0, dateOffset) = Total1
    ActiveSheet.Range("A1").Offset(0, dateOffset+1) = Total2
    

    etc

    EDIT 2:

    Finding a date

    Based on your comment: you can find a date in another sheet as follows:

    Dim selectedDate As String
    Dim rangeFound As Range
    selectedDate = ActiveCell.Value
    Set rangeFound = Sheet2.Cells.Find(CDate(selectedDate))
    

    Obviously you'd change Sheet2 to whatever you wanted. That will give you the address of the cell containing the date on the summary sheet. If you use rangeFound.Address you will get a value similar to $D$9. If you use rangeFound.Row and rangeFound.Column you will get the actual row and column numbers. Note that you also have the address of the active cell through the same principle (e.g. ActiveCell.Row). From there, you can copy values using something like

    If Not (rangeFound Is Nothing) Then
        ActiveCell.Offset(0, 1) = rangeFound.Offset(0, 1)
        ActiveCell.Offset(0, 2) = rangeFound.Offset(0, 2)
    End If