#Find a range in excel vba code

See the “Use the Resize Property to Change the Size of a Range” section later in this chapter for more information on the Resize property.Ĭells properties can be used as parameters in the Range property.

#Find a range in excel vba code

Instead of trying to type the range address, the new code uses the Cells and Resize properties to find the required cell, based on the active cell. But, how else can you do it? FinalRow = Cells(Rows.Count, 1).End(xlUp).Row This little piece of code, which loops through rows and bolds the cells in Columns A through E, is awkward to read and write. Range('A' & i & ':E' & i).Font.Bold = True If you are learning to code only from the recorder, you might be tempted to write code like this: FinalRow = Cells(Rows.Count, 1).End(xlUp).Row Select for a single cell and Range('A1:C5').Select for a range of cells.

The macro recorder usually uses something like Range('A1'). The ability to use numeric values for parameters is particularly useful if you need to loop through rows or columns. Both of the following lines refer to cell C5: Cells.Item(5,'C')īecause the Item property is the default property of the RANGE object, you can shorten these lines as follows: Cells(5,'C') You must use a numeric value for Row, but you may use the numeric value or string value for Column. The syntax for using the Item property with the Cells property is as follows: Cells.Item(Row,Column) The Item property enables you to refer to a specific cell relative to the Range object. However, the Cells property has an Item property that makes the Cells property very useful. This line refers to the original Range object. Using the Cells property with the Range object might seem redundant: Range('A1:D5').Cells For example, this line selects all the cells of the active sheet: Cells.Select The Cells property refers to all the cells of the specified range object, which can be a worksheet or a range of cells.

Learn More Buy Use the Cells Property to Select a Range Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)