How To Select Specific Cells In Excel

Download Article

Download Commodity

This wikiHow teaches you how to start using Visual Basic procedures to select data in Microsoft Excel. As long every bit yous're familiar with basic VB scripting and using more than advanced features of Excel, you'll notice the pick procedure pretty straight-frontwards.

Select ane cell on the current worksheet. Let'southward say you want to select cell E6 with Visual Basic. You can do this with either of the following options:^[1]

                                            ActiveSheet                      .                      Cells                      (                      half dozen                      ,                      five                      ).                      Select

                                            ActiveSheet                      .                      Range                      (                      "E6"                      ).                      Select

Select one cell on a different worksheet in the same workbook. Permit's say our example cell, E6, is on a sail called Sheet2. You can use either of the following options to select it:

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

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

Advertizement

iii

Select i cell on a worksheet in a different workbook. Let'south say you want to select a prison cell from Sheet1 in a workbook called BOOK2.XLS. Either of these two options should practise the flim-flam:

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

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

Select a cell relative to some other cell. You tin use VB to select a prison cell based on its location relative to the agile (or a different) jail cell. Just be sure the jail cell exists to avert errors. Here'south how to utilise :

Select the cell three rows below and four columns to the left of the active prison cell:

                                                    ActiveCell                          .                          Start                          (                          3                          ,                          -                          4                          ).                          Select

Select the cell five rows beneath and iv columns to the right of prison cell C7:

                                                    ActiveSheet                          .                          Cells                          (                          7                          ,                          3                          ).                          Start                          (                          5                          ,                          iv                          ).                          Select

Select a range of cells on the active worksheet. If you lot wanted to select cells C1:D6 on the electric current sheet, y'all can enter any of the following 3 examples:

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

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

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

two

Select a range from another worksheet in the same workbook. You could use either of these examples to select cells C3:E11 on a sail called Sheet3:

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

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

three

Select a range of cells from a worksheet in a different workbook. Both of these examples would select cells E12:F12 on Sheet1 of a workbook called BOOK2.XLS:

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

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

Select a named range. If you've assigned a proper noun to a range of cells, you'd use the aforementioned syntax as steps 4-6, but you'd replace the range address (e.g., "E12", "F12") with the range's name (east.thousand., "Sales"). Here are some examples:

On the active sail:

                                                    ActiveSheet                          .                          Range                          (                          "Sales"                          ).                          Select

Different canvass of same workbook:

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

Different workbook:

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

Select a range relative to a named range. The syntax varies depending on the named range'south location and whether yous want to suit the size of the new range.

If the range you desire to select is the same size equally one chosen Test5 just is shifted four rows down and three columns to the right, you'd use:

                                                    ActiveSheet                          .                          Range                          (                          "Test5"                          ).                          Starting time                          (                          four                          ,                          three                          ).                          Select

If the range is on Sheet3 of the same workbook, actuate that worksheet commencement, so select the range like this:

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

Select a range and resize the selection. You lot can increase the size of a selected range if you lot need to. If y'all wanted to select a range called Database' and and so increase its size past five rows, you'd use this syntax:

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

seven

Select the union of two named ranges. If y'all have two overlapping named ranges, yous can use VB to select the cells in that overlapping area (called the "wedlock"). The limitation is that you tin can only do this on the active sheet. Allow's say you want to select the union of a range chosen Great and 1 called Terrible:

                                                    Awarding                          .                          Union                          (                          Range                          (                          "Peachy"                          ),                          Range                          (                          "Terrible"                          )).                          Select

If y'all want to select the intersection of two named ranges instead of the overlapping area, but replace Application.Union with Application.Intersect.

Advertizement

Employ this instance data for the examples in this method. This chart full of example data, courtesy of Microsoft, volition help yous visualize how the examples deport:^[2]

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

Select the concluding cell at the lesser of a face-to-face cavalcade. The following example will select cell A4:

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

Select the get-go blank jail cell beneath a column of contiguous cells. The following instance volition select A5 based on the chart above:

                                            ActiveSheet                      .                      Range                      (                      "A1"                      ).                      Finish                      (                      xlDown                      ).                      Kickoff                      (                      i                      ,                      0                      ).                      Select

Select a range of continuous cells in a column. Both of the following examples will select the range A1:A4:

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

                                            ActiveSheet                      .                      Range                      (                      "A1:"                      &                      ActiveSheet                      .                      Range                      (                      "A1"                      ).                      Stop                      (                      xlDown                      ).                      Accost                      ).                      Select

Select a whole range of not-contiguous cells in a column. Using the data table at the height of this method, both of the following examples will select A1:A6:

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

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

Inquire a Question

200 characters left

Include your e-mail address to get a message when this question is answered.

Submit

Advert

Video

The "ActiveSheet" and "ActiveWorkbook" backdrop tin usually be omitted if the active sheet and/or workbook(south) are unsaid.

Nigh This Article

Commodity Summary X

1. Employ ActiveSheet.Range("E6").Select to select E6 on the agile sail.
2. Utilise Awarding.Goto (ActiveWorkbook.Sheets("Sheet2").Range("E6")) to select E6 on Sheet2.
3. Add together Workbooks("BOOK2.XLS") to the last pace to specify that the canvass is in BOOK2.XLS.

Did this summary help you?

Cheers to all authors for creating a page that has been read 161,263 times.