Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex it is enough just to provide the row number. To get and entire row of a specified Range you need to use the EntireRow property. There are a couple of ways to obtain Worksheet rows in VBA: Getting an entire row or column

The Excel VBA Range object allows you to select single or multiple rows as well as single or multiple columns. The Cells property is in fact a Range object not a separate data type.Įxcel facilitates a Cells function that allows you to obtain a cell from within the ActiveSheet, current top-most worksheet.Ĭells are Ranges which means they are not a separate data type:Īs we all know an Excel Worksheet is divided into Rows and Columns. 'Let us assume A1 contains the formula '=10+20'ĭebug.Print Range('A1').Value 'Returns: 30ĭebug.Print Range('A1').Formula 'Returns: =10+20Ī Worksheet Cells property is similar to the Range property but allows you to obtain only a SINGLE CELL, based on its row and column index. For example a cell with a formula of =10+20 will have the same Formula property. For example a cell with the formula =10+20 has an evaluated value of 20.Ī Range Formula is the formula provided in the cell or range of cells. The Range object contains a variety of properties with the main one being it’s Value and an the second one being its Formula.Ī Range Value is the evaluated property of a cell or a range of cells. This may reference a Worksheet from within a Workbook external to the Workbook in which the macro is executed as Active references simply the currently top-most worksheet. The ActiveWorkbook is not same to ThisWorkbook. Sheets('Sheet1').Range('A1').Select 'Will always select items from Worksheet named Sheet1 You might want to define the Worksheet reference by Range if you want your reference values from a specifc Worksheet: So beware as depending on your ActiveWorksheet the Range object will return values local to your worksheet: The Range object defaults to your ActiveWorksheet. Set myRange = Range('A1') 'Need to use Set to define myRange As the VBA Range is an object you need to use the Set statement: The Range is a separate object variable and can be declared as other variables. Select a cell or Range of cells using the Select method. Range('A1:B2').Activate 'Activate the cells and show them on your screen (will switch to Worksheet and/or scroll to this range. Range('A1:B2').Select 'Select the Cells A1:B2 in your Excel Worksheet Set r= Range(Range('A1'), Range ('B1')) 'Range of 2 cells A1 and B1 Set r = Range('A1:B2') 'Square Range of 4 cells - A1,A2,B1,B2 The Range is a Worksheet property which allows you to select any subset of cells, rows, columns etc.

Visual basic for excel tutorial