The macro recorder will often create a macro that uses the Select method and the Selection property. The following Sub procedure was created using the macro recorder, and it illustrates how Select and Selection work together.or example, if you want to enter a formula in cell D6 using Visual Basic, you don't need to select the range D6.
Some examples of how to avoid select. Use Dim 'd variables. Set the variable to the required range. There are many ways to refer to a single-cell range. or a multi-cell range. All the above examples refer to cells on the active sheet. 1. Selection can be anything in the worksheet so might as well test first the type of the object before assigning it to a variable since you explicitly declared it as Range. – L42 May 19 at 22:19. add a comment |. up vote -2 down vote. Always state the workbook, worksheet and the cell/range. Using the Select Method and the Selection Property. The Select method activates sheets and objects on sheets; the Selection property returns an object that represents the current selection on the active sheet in the active workbook. For example, if you want to enter a formula in cell D6 using Visual Basic, you don't need to select the range D6. You just need to return the Range object and then set the Formula property to the formula you want, as shown in the following example. To select the named range Test on the active worksheet, you can use either of the following examples: To select the named range Test on another worksheet in the same workbook, you can use the following example: Or, you can activate the worksheet, and then use method 7 above to select the named range: To select the named range Test on a worksheet in a different workbook, you can use the following example: In order to select a rectangular range of cells around a cell, use the CurrentRegion method. The range selected by the CurrentRegion method is an area bounded by any combination of blank rows and blank columns. The following is an example of how to use the CurrentRegion method: This code will select cells A1 through C4. The macro recorder will often create a macro that uses the Select method and the Selection property. The following Sub procedure was created using the macro recorder, and it illustrates how Select and Selection work together. For example, if you want to enter a formula in cell D6 using Visual Basic, you don't need to select the range D6. You just need to return the Range object and then set the Formula property to the formula you want, as shown in the following example. Selecting Cells on the Active Worksheet. If you use the Select method to select cells, be aware that Select works only on the active worksheet. If you run your Sub procedure from the module, the Select method will fail unless your procedure activates the worksheet before using the Select method on a range of cells. For example, if you want to enter a formula in cell D6 using Visual Basic, you don't need to select the range D6. You just need to return the Range object and then set the Formula property to the formula you want, as shown in the following example. To select cell D5 on the active worksheet, you can use either of the following examples: To select cell E6 on another worksheet in the same workbook, you can use either of the following examples: Or, you can activate the worksheet, and then use method 1 above to select the cell: To select cell F7 on a worksheet in a different workbook, you can use either of the following examples: In order to select a rectangular range of cells around a cell, use the CurrentRegion method. The range selected by the CurrentRegion method is an area bounded by any combination of blank rows and blank columns. The following is an example of how to use the CurrentRegion method: This code will select cells A1 through C4. Excel had to record this macro in two steps because I really did (1) select cell D9, and then I did (2) enter a value into that cell. But in your VBA programs, you seldom need to select an object (like a worksheet, a range, a chart, or whatever) in order to do something with that object. Instead, you can specify the object and then apply the action directly. To illustrate, this macro enters “x” in the workbook, worksheet, and cell shown, without activating Book1, Sheet1, or cell D9: Similarly, you could perform two actions, like this: There are at least two advantages to writing code that doesn’t select the objects you’re working with.
http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros https://msdn.microsoft.com/en-us/library/office/aa221576(v=office.11).aspx https://support.microsoft.com/en-us/kb/291308 https://msdn.microsoft.com/en-us/library/office/aa221576(v=office.11).aspx https://msdn.microsoft.com/en-us/library/office/aa221576(v=office.11).aspx https://support.microsoft.com/en-us/kb/291308 http://www.exceluser.com/blog/146/you-usually-dont-need-to-select-an-object-in-excel-vba.html
how to select an object without selecting is manually using vba