The information in this article applies to:
SUMMARY
When you create a Microsoft Visual Basic for Applications macro or
procedure, you can choose from a variety of methods for selecting cells,
ranges, and named ranges. This article contains sample code that
demonstrates some of these methods.
MORE INFORMATIONMicrosoft provides programming examples for illustration only, without warranty
either expressed or implied, including, but not limited to, the implied warranties of
merchantability and/or fitness for a particular purpose. This article assumes that you
are familiar with the programming language being demonstrated and the tools used to
create and debug procedures. Microsoft support professionals can help explain the functionality
of a particular procedure, but they will not modify these examples to provide added
functionality or construct procedures to meet your specific needs. If you have limited
programming experience, you may want to contact a Microsoft Certified Solution Provider
or the Microsoft fee-based consulting line at (800) 936-5200. For more information about
Microsoft Certified Solution Providers, please see the following page on the World Wide Web:
http://www.microsoft.com/mcsp/For more information about the support options available from Microsoft, please see the following page on the World Wide Web: http://www.microsoft.com/support/supportnet/overview/overview.aspThe examples in this article use the Visual Basic methods listed in the following table.
The examples in this article use the properties in the following table.
1: How to Select a Cell on the Active WorksheetTo select cell D5 on the active worksheet, you can use either of the following examples:
Back to Top
2: How to Select a Cell on Another Worksheet in the Same WorkbookTo 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:
Back to Top
3: How to Select a Cell on a Worksheet in a Different WorkbookTo select cell F7 on a worksheet in a different 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:
Back to Top
4: How to Select a Range of Cells on the Active WorksheetTo select the range C2:D10 on the active worksheet, you can use any of the following examples:
Back to Top
5: How to Select a Range of Cells on Another Worksheet in the Same WorkbookTo select the range D3:E11 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 4 above to select
the range:
Back to Top
6: How to Select a Range of Cells on a Worksheet in a Different WorkbookTo select the range E4:F12 on a worksheet in a different workbook, you can use either of the following examples:
Or, you can activate the worksheet, and then use method 4 above to select
the range:
Back to Top
7: How to Select a Named Range on the Active WorksheetTo select the named range "Test" on the active worksheet, you can use either of the following examples:
Back to Top
8: How to Select a Named Range on Another Worksheet in the Same WorkbookTo 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:
Back to Top
9: How to Select a Named Range on a Worksheet in a Different WorkbookTo select the named range "Test" on a worksheet in a different workbook, you can use the following example:
Or, you can activate the worksheet, and then use method 7 above to select
the named range:
Back to Top
10: How to Select a Cell Relative to the Active CellTo select a cell that is five rows below and four columns to the left of the active cell, you can use the following example:
To select a cell that is two rows above and three columns to the right of
the active cell, you can use the following example:
NOTE: An error will occur if you attempt to select a cell that is "off the
worksheet." The first example shown above will return an error if the
active cell is in columns A through D, since moving four columns to the
left would take the active cell to an invalid cell address.
Back to Top 11: How to Select a Cell Relative to Another (Not the Active) CellTo select a cell that is five rows below and four columns to the right of cell C7, you can use either of the following examples:
Back to Top
12: How to Select a Range of Cells Offset from a Specified RangeTo select a range of cells that is the same size as the named range "Test" but that is shifted four rows down and three columns to the right, you can use the following example:
If the named range is on another (not the active) worksheet, activate that
worksheet first, and then select the range using the following example:
Back to Top
13: How to Select a Specified Range and Resize the SelectionTo select the named range "Database" and then extend the selection by five rows, you can use the following example:
Back to Top
14: How to Select a Specified Range, Offset It, and Then Resize ItTo select a range four rows below and three columns to the right of the named range "Database" and include two rows and one column more than the named range, you can use the following example:
Back to Top
15: How to Select the Union of Two or More Specified RangesTo select the union (that is, the combined area) of the two named ranges "Test" and "Sample," you can use the following example:
Note that both ranges must be on the same worksheet for this example to
work. Note also that the Union method does not work across sheets. For
example, this line works fine:
but this line:
returns the error message:
Back to Top 16: How to Select the Intersection of Two or More Specified RangesTo select the intersection of the two named ranges "Test" and "Sample," you can use the following example:
Note that both ranges must be on the same worksheet for this example to
work.
Back to Top Examples 17-21 in this article refer to the following example set of data. Each example states the range of cells in the sample data that would be selected.
17: How to Select the Last Cell of a Column of Contiguous DataTo select the last cell in a contiguous column, use the following example:
When this code is used with the sample table, cell A4 will be selected.
Back to Top 18: How to Select the Blank Cell at Bottom of a Column of Contiguous DataTo select the cell below a range of contiguous cells, use the following example:
When this code is used with the sample table, cell A5 will be selected.
Back to Top 19: How to Select an Entire Range of Contiguous Cells in a ColumnTo select a range of contiguous cells in a column, use one of the following examples:
When this code is used with the sample table, cells A1 through A4 will be
selected.
Back to Top 20: How to Select an Entire Range of Non-Contiguous Cells in a ColumnTo select a range of cells that are non-contiguous, use one of the following examples:
When this code is used with the sample table, it will select cells A1
through A6.
Back to Top 21: How to Select a Rectangular Range of CellsIn 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. Other examples to select the
same range of cells are listed below:
In some instances, you may want to select cells A1 through C6. In this
example, the CurrentRegion method will not work because of the blank line
on Row 5. The following examples will select all of the cells:
-or-
Back to Top
22. How to Select Multiple Non-Contiguous Columns of Varying LengthTo select multiple non-contiguous columns of varying length, use the following sample table and macro example:
When this code is used with the sample table, cells A1:A3 and C1:C6 will be
selected.Back to Top NOTES ON THE EXAMPLES
you can use:
where the value of myVar is "Sheet1".REFERENCESFor more information about selecting cells in a Visual Basic macro, in the Visual Basic Editor, click
Microsoft Visual Basic Help on the Help menu, type
Selecting and Activating cells in the Office Assistant or the Answer Wizard, and
then click Search to view the topic. Q212536 OFF2000: How to Run Sample Code from Knowledge Base Articles Additional query words: howto contiguous how to discontiguous non-contiguous nonadjacent non-adjacent XL2000
Keywords : kbprg kbdta kbdtacode OffVBA PgmOthr PgmHowto KbVBA |
Last Reviewed: October 8, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |