Content

• To step
• Method 1 of 3: Select individual cells
• Method 2 of 3: Select ranges
• Method 3 of 3: Select the data in "consecutive" and "non-consecutive" columns
• Tips

This wikiHow teaches you how to use Visual Basic to select data in Microsoft Excel. As long as you are familiar with the basics of VB scripts and the use of more advanced functions of Excel, you will find the selection scripts quite easy.

To step

Method 1 of 3: Select individual cells

1. Select a cell in the current worksheet. Suppose you want to select cell E6 with Visual Basic. You can do this with one of the following commands:
   

   ActiveSheet.Cells (6, 5) .Select
   

   ActiveSheet.Range ("E6"). Select

2. Select a cell in another worksheet in the same workbook. Let's say our example cell, E6, is on a sheet called Sheet2. You can use one of the following methods to select it:
   

   Application.Goto ActiveWorkbook.Sheets ("Sheet2"). Cells (6, 5)

   Application.Goto (ActiveWorkbook.Sheets ("Sheet2"). Range ("E6"))

3. Select a cell on a worksheet in another workbook. Let's say you want to select a cell from Sheet1 in a workbook named BOOK2.XLS. One of these two ways should work:

   Application.Goto Workbooks ("BOOK2.XLS"). Sheets ("Sheet1"). Cells (2,1)

   Application.Goto Workbooks ("BOOK2.XLS"). Sheets ("Sheet1"). Range ("A2")

4. Select a cell relative to another cell. You can use VB to select a cell based on its location relative to the active (or another) cell. Just make sure the cell exists to avoid mistakes. This is done as follows:
   • Select the cell three rows below and four columns to the left of the active cell:

     ActiveCell.Offset (3, -4) .Select

   • Select the cell five rows below and four columns to the right of cell C7:

     ActiveSheet.Cells (7, 3) .Offset (5, 4) .Select

Method 2 of 3: Select ranges

1. Select a range of cells on the active worksheet. To select cells C1: D6 on the current worksheet, use one of the following three methods:

   ActiveSheet.Range (Cells (1, 3), Cells (6, 4)). Select

   ActiveSheet.Range ("C1: D6"). Select

   ActiveSheet.Range ("C1", "D6"). Select

2. Select a series from another worksheet in the same workbook. You can use one of these examples to select cells C3: E11 on a sheet called Sheet3:

   Application.Goto ActiveWorkbook.Sheets ("Sheet3"). Range ("C3: E11")

   Application.Goto ActiveWorkbook.Sheets ("Sheet3"). Range ("C3", "E11")

3. Select a range of cells from a worksheet in another workbook. Both examples should select cells E12: F12 on Sheet1 of a workbook named BOOK2.XLS:

   Application.Goto Workbooks ("BOOK2.XLS"). Sheets ("Sheet1"). Range ("E12: F12")

   Application.Goto Workbooks ("BOOK2.XLS"). Sheets ("Sheet1"). Range ("E12", "F12")

4. Select a named range. If you have assigned a name to a range of cells, use the same syntax as in steps 4-6, but replace the address of the range (eg 'E12', 'F12 ") with the name of the range (eg . 'Sales'). Here are some examples:
   
   • On the active worksheet:

     ActiveSheet.Range ("Sales"). Select

   • Other worksheet of the same workbook:

     Application.Goto ActiveWorkbook.Sheets ("Sheet3"). Range ("Sales")

   • Other workbook:

     Application.Goto Workbooks ("BOOK2.XLS"). Sheets ("Sheet1"). Range ("Sales")

5. Select a range relative to a named range. The syntax varies depending on the location of the mentioned range and whether you want to adjust the size of the new range.
   • If the range you want to select is the same size as a die Test5 is called, but shifted four rows down and three columns to the right, then use the following code:

     ActiveSheet.Range ("Test5"). Offset (4, 3) .Select

   • If the range is on Sheet3 of the same workbook, please activate that worksheet first, then select the range as follows:

     Sheets ("Sheet3"). Activate ActiveSheet.Range ("Test"). Offset (4, 3) .Select

6. Select a range and resize the selection. You can increase the size of a selected range if needed. If you want to select a range that Database and then want to increase the size by five rows, you would use this syntax:

   Range ("Database"). Select Selection.Resize (Selection.Rows.Count + 5, _Selection.Columns.Count) .Select

7. Select the union of two named series. If you have two overlapping named ranges, then you can use VB to select the cells in that overlapping region (the 'union'). The limitation is that you can only do this on the active worksheet. Let's say you have the union of a range called Good and one named Bad want to select:

   • Application.Union (Range ("Good"), Range ("Bad")). Select

   • If you want to select the subset of two mentioned ranges instead of the overlapping area, just replace Application.Union through Application.Intersect.

Method 3 of 3: Select the data in "consecutive" and "non-consecutive" columns

1. Use this sample data for the examples in this method. This chart full of sample data, courtesy of Microsoft, will help you visualize how the examples behave:

   A1: Name	B1: Sale	C1: Quantity
   A2: a	B2: € 10	C2: 5
   A3: b	B3:	C3: 10
   A4: c	B4: € 10	C4: 5
   A5:	B5:	C5:
   A6: Total	B6: € 20	C6: 20

2. Select the last cell at the bottom of a contiguous column. In the following example, cell A4 is selected:

   ActiveSheet.Range ("A1"). End (xlDown) .Select

3. Select the first empty cell below a column of contiguous cells. The following example selects A5 based on the chart above:

   ActiveSheet.Range ("A1"). End (xlDown) .Offset (1.0) .Select

4. Select a range of consecutive cells in a column. In both of the following examples, the range A1: A4 is selected:

   ActiveSheet.Range ("A1", ActiveSheet.Range ("a1"). End (xlDown)). Select

   ActiveSheet.Range ("A1:" & ActiveSheet.Range ("A1"). End (xlDown) .Address) .Select

5. Select a whole range of non-consecutive cells in a column. Using the data table at the top of this method, both of the following examples will select A1: A6:

   ActiveSheet.Range ("A1", ActiveSheet.Range ("A65536"). End (xlUp)). Select

   ActiveSheet.Range ("A1", ActiveSheet.Range ("A65536"). End (xlUp)). Select

Tips

• The properties "ActiveSheet" and "ActiveWorkbook" can usually be omitted if the active sheet and / or workbook (s) are implied.