How to Select Cells and Ranges in Excel Visual Basic

Method 1 of 3:

Selecting Individual Cells

Select one cell on the current worksheet. Let's say you want to select cell E6 with Visual Basic. You can do this with either of the following options:^[1]
```
ActiveSheet.Cells(6, 5).Select 
```
```
ActiveSheet.Range("E6").Select 
```
Select one cell on a different worksheet in the same workbook. Let's say our example cell, E6, is on a sheet called Sheet2. You can use either of the following options to select it:
```
Application.Goto ActiveWorkbook.Sheets("Sheet2").Cells(6, 5) 
```
```
Application.Goto (ActiveWorkbook.Sheets("Sheet2").Range("E6")) 
```
Select one cell on a worksheet in a different workbook. Let's say you want to select a cell from Sheet1 in a workbook called BOOK2.XLS. Either of these two options should do the trick:
```
Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet1").Cells(2,1) 
```
```
Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet1").Range("A2") 
```
Select a cell relative to another cell. You can use VB to select a cell based on its location relative to the active (or a different) cell. Just be sure the cell exists to avoid errors. Here's how to use :
1. Select the cell three rows below and four columns to the left of the active cell:
  ActiveCell.Offset(3, -4).Select
2. 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:

Selecting Ranges

Select a range of cells on the active worksheet. If you wanted to select cells C1:D6 on the current sheet, you can enter any of the following three examples:
```
ActiveSheet.Range(Cells(1, 3), Cells(6, 4)).Select 
```
```
ActiveSheet.Range("C1:D6").Select 
```
```
ActiveSheet.Range("C1", "D6").Select 
```
Select a range from another worksheet in the same workbook. You could use either 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") 
```

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 name to a range of cells, you'd use the same syntax as steps 4-6, but you'd replace the range address (e.g., "E12", "F12") with the range's name (e.g., "Sales"). Here are some examples:
1. On the active sheet:
  ActiveSheet.Range("Sales").Select
2. Different sheet of same workbook:
  Application.Goto ActiveWorkbook.Sheets("Sheet3").Range("Sales")
3. Different workbook:
  Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet1").Range("Sales")
Select a range relative to a named range. The syntax varies depending on the named range's location and whether you want to adjust the size of the new range.
1. If the range you want to select is the same size as one called Test5 but is shifted four rows down and three columns to the right, you'd use:
  ActiveSheet.Range("Test5").Offset(4, 3).Select
2. If the range is on Sheet3 of the same workbook, activate that worksheet first, and then select the range like this:
  Sheets("Sheet3").Activate ActiveSheet.Range("Test").Offset(4, 3).Select
Select a range and resize the selection. You can increase the size of a selected range if you need to. If you wanted to select a range called Database' and then increase its size by 5 rows, you'd use this syntax:
```
Range("Database").Select Selection.Resize(Selection.Rows.Count + 5, _Selection.Columns.Count).Select 
```
Select the union of two named ranges. If you have two overlapping named ranges, you can use VB to select the cells in that overlapping area (called the "union"). The limitation is that you can only do this on the active sheet. Let's say you want to select the union of a range called Great and one called Terrible:
1. Application.Union(Range("Great"), Range("Terrible")).Select
2. If you want to select the intersection of two named ranges instead of the overlapping area, just replace Application.Union with Application.Intersect.

Method 3 of 3:

Selecting from Contiguous and Non-Contiguous Data in Columns

Use this example data for the examples in this method. This chart full of example data, courtesy of Microsoft, will help you visualize how the examples behave:^[2]
A1: Name B1: Sales C1: QuantityA2: 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
Select the last cell at the bottom of a contiguous column. The following example will select cell A4:
```
ActiveSheet.Range("A1").End(xlDown).Select 
```
Select the first blank cell below a column of contiguous cells. The following example will select A5 based on the chart above:
```
ActiveSheet.Range("A1").End(xlDown).Offset(1,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"). End(xlDown).Address).Select

Select a whole range of non-contiguous 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 
```

Marvin Fry

Update 05 March 2020

You should read it

May be interested

Microsoft Visual Basic Error in Excel, How to Fix This Error?
hello! tipsmake will help you solve the problem of excel having microsoft visual basic errors. maybe my suggestions will help you
Instructions on how to create diagonal lines in Excel box
manipulating 1 cell into 2 diagonal triangles with a line on excel is a very basic operation and is often performed during the process of creating tables on excel.
How to color formula cells in Excel automatically
coloring formula cells in excel helps us quickly identify cells that use formulas in long data tables.
Tips for adding in Excel you need to know
cong is one of the basic excel operations.
How to name, comment and protect cells in Excel
instructions on how to name, note and protect cells in excel. 1. name the data cell. - normally, the data cell with the default name is the combination of the column and row order that makes up the cell. for example, cell b5 is named b5. - to change the name for a cell, do as follows: + click to select
How to Lock Cells in Excel
locking cells in an excel spreadsheet can prevent any changes from being made to the data or formulas that reside in those particular cells. cells that are locked and protected can be unlocked at any time by the user who initially locked...
Extremely simple instructions on how to fill in blank cells with 0 in Excel
when working in excel, you may encounter a situation where your spreadsheet has many blank cells and need to fill in values in those cells. don't worry, read the article below to learn how to fill in blank cells with 0 in excel.
How to merge cells in Excel - Instructions to merge cells in Excel 2010, 2013, 2016
how to merge cells in excel - instructions to merge cells in excel 2010, 2013, 2016. while working with excel, sometimes you want to combine cells together to make the cell larger in size and better suited to the content. than. but you do not know how to combine cells in exc
List of common shortcuts for Google Sheets on Chrome OS (Part 3)
google sheets consists of worksheets, each sheet consists of multiple cells created by rows and columns, the toolbar supports basic tools such as formatting sheets, cells, text, inserting charts, images, ... .
How to split 1 cell into 2 cells in Excel - Split cells in Excel
how to split 1 cell into 2 cells in excel - split cells in excel. depending on different purposes, you want to split a cell into two cells to enter two different data in one cell and still be able to calculate normally as other cells.