‘Rows.count returns the last row of the worksheet (which in Excel 2007 is 1,048,576); Cells(Rows.count, “B”) returns the cell B1048576, ie. last cell in column B, and the code starts from this cell moving upwards; the code is bascially executing Range(“B1048576”).End(xlUp), and Range(“B1048576”).End(xlUp).Row finally returns the last row number.
lastRow = ActiveSheet.Cells(Rows.Count, “B”).End(xlUp).Row

MsgBox lastRow

End Sub

Sub LastRowWithData_xlUp_2()

‘use End(xlUp) to determine Last Row with Data, in one column (column B)

Dim lastRow As Long

lastRow = ActiveSheet.Range(“B” & Rows.Count).End(xlUp).Row

MsgBox lastRow

End Sub

Notes:

– Returns last used row (with data) number in a specific column. In case of a blank column, it will return the value 1. It does not count a formatted cell but with no data. Data includes constants & formulas.

– Formula fails if you actually use the very last row (viz. row 65,536 in Excel 2003 or 1,048,576 in Excel 2007) – it will not consider this row.

– If the last row with data is hidden, this row is ignored and not counted.

– End(xlUp) is one of the most commonly used methods to determine the last used row, with data.

Range.End Property:

In VBA you will often need to refer to a cell at the end of a block, for example, to determine the last used row in a range. The End property is used with reference to a Range object and returns the cell which is at the end of the region in which the the referenced range is contained. The property returns a Range object, which is the cell at the end of the block in a specified direction, and is similar to pressing CTRL+UP ARROW, CTRL+DOWN ARROW, CTRL+LEFT ARROW or CTRL+RIGHT ARROW. Syntax: RangeObject.End(Direction). It is necessary to specify the Direction argument, which indicates the direction of movement viz. xlDown (value -4121), xlToLeft (-4159), xlToRight (-4161) and xlUp (-4162). End(xlUp) is a commonly used method to determine the last used row, with data. End(xlDown) gets the last cell before blank in a column, whereas End(xlToRight) gets the last cell before blank in a row.

Row & Column Properties of the Range object:

To return the number of the first row in a range, use the Range.Row Property. If the specified range contains multiple areas, this property will return the number of the first row in the first area (Areas property has been explained in detail later in this section). Syntax: RangeObject.Row. To return the number of the first column in a range, use the Range.Column Property. If the specified range contains multiple areas, this property will return the number of the first column in the first area. Syntax: RangeObject.Column.

————————————————————————————————————-

Use End(xlToLeft) to determine Last Column with Data, in one row

Sub LastColumnWithData_xlToLeft()

‘use End(xlToLeft) to determine Last Column with Data, in one row (row number 2)

Dim lastColumn As Integer

lastColumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column

MsgBox lastColumn

End Sub

Notes:

– Returns last used column (with data) number in a specific row. This formula will return the value 1 for an empty row. It does not count a formatted cell but with no data. Data includes constants & formulas.

– Formula fails if you actually use the very last column (viz. column 256 in Excel 2003 or 16384 in Excel 2007) – it will not consider this column.

– If the last column with data is hidden, this column is ignored and not counted.

– End(xlToLeft) is one of the most commonly used methods to determine the last used column, with data.

————————————————————————————————————-

UsedRange property to find the last used row number in a worksheet

Sub LastUsedRow_UsedRange_1()

‘UsedRange property to find the last used row number in a worksheet

Dim lastRow As Long

lastRow = ActiveSheet.UsedRange.Row – 1 + ActiveSheet.UsedRange.Rows.Count

MsgBox lastRow

End Sub

Sub LastUsedRow_UsedRange_2()

‘UsedRange property to find the last used row number in a worksheet

Dim lastRow As Long

lastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

MsgBox lastRow

End Sub

Notes:

– This determines the last used (cells with data or formatted) row in a worksheet. In case of a blank worksheet it will return the value 1.

– Hidden rows are also counted.

– This also counts formatted cells with no data, viz. if you apply Date format to a cell. In this case clearing the content/format might not be enough to re-set, you will have to delete the particular row.

UsedRange property:

To return the used range in a worksheet, use the Worksheet.UsedRange Property. Syntax: WorksheetObject.UsedRange. Using the UsedRange property may also count formatted cells with no data or cells with data earlier whose content has been deleted, and in this case might include seemingly visible blank cells. For example, if you apply Date format to a cell, in this case clearing the content/format might not be enough to re-set, you will have to delete the particular row.

Row & Column Properties of the Range object:

Rows & Columns Properties of the Range object:

Use the Range.Rows Property (Syntax: RangeObject.Rows) to refer to rows in a specified range. Example1: color cells from all rows of the specified range ie. B2 to D4: Worksheets(“Sheet1”).Range(“B2:D4”).Rows.Interior.Color = vbYellow. Example2: color cells from first row of the range only ie. B2 to D2: Worksheets(“Sheet1”).Range(“B2:D4”).Rows(1).Interior.Color = vbGreen. If the specified range object contains multiple areas, the rows from the first area only will be returned by this property (Areas property has been explained in detail later in this section). Take the example of 2 areas in the specified range, first area being “B2:D4” and the second area being “F3:G6” – the following code will color cells from first row of the first area only ie. cells B2 to D2: Worksheets(“Sheet1”).Range(“B2:D4, F3:G6”).Rows(1).Interior.Color = vbRed. Omitting the object qualifier will default to active sheet – following will apply color to row one of the ActiveSheet: Rows(1).Interior.Color = vbRed.

Use the Range.Columns Property (Syntax: RangeObject.Columns) to refer to columns in a specified range. Example1: color cells from all columns of the specified range ie. B2 to D4: Worksheets(“Sheet1”).Range(“B2:D4”).Columns.Interior.Color = vbYellow. Example2: color cells from first column of the range only ie. B2 to B4: Worksheets(“Sheet1”).Range(“B2:D4”).Columns(1).Interior.Color = vbGreen. If the specified range object contains multiple areas, the columns from the first area only will be returned by this property (Areas property has been explained in detail later in this section). Take the example of 2 areas in the specified range, first area being “B2:D4” and the second area being “F3:G6” – the following code will color cells from first column of the first area only ie. cells B2 to B4: Worksheets(“Sheet1”).Range(“B2:D4, F3:G6”).Columns(1).Interior.Color = vbRed. Omitting the object qualifier will default to active sheet – following will apply color to column A of the ActiveSheet: Columns(1).Interior.Color = vbRed.

————————————————————————————————————-

UsedRange property to find the last used column number in a worksheet

Sub LastUsedColumn_UsedRange_1()

‘UsedRange property to find the last used column number in a worksheet

Dim lastColumn As Integer

lastColumn = ActiveSheet.UsedRange.Column – 1 + ActiveSheet.UsedRange.Columns.Count

MsgBox lastColumn

End Sub

Sub LastUsedColumn_UsedRange_2()

‘UsedRange property to find the last used column number in a worksheet

Dim lastColumn As Integer

lastColumn = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

MsgBox lastColumn

End Sub

Notes:

– This determines the last used (cells with data or formatted) column in a worksheet. In case of a blank worksheet it will return the value 1.

– Hidden columns are also counted.

————————————————————————————————————-

UsedRange property to find number of used rows in a worksheet

Sub UsedRows_UsedRange()

‘UsedRange property to find number of used rows in a worksheet

Dim usedRows As Long

usedRows = ActiveSheet.UsedRange.Rows.Count

MsgBox usedRows

End Sub

Notes:

– Counts the number of used (cells with data or formatted) rows in the worksheet, starting from the first used row till the last used row. Does not give row no of last used row.

– Hidden rows are also counted.

– In case of a blank worksheet it will return the value 1.

————————————————————————————————————-

UsedRange property to find number of used columns in a worksheet

Sub UsedColumns_UsedRange()

‘UsedRange property to find number of used columns in a worksheet

Dim usedColumns As Integer

usedColumns = ActiveSheet.UsedRange.Columns.Count

MsgBox usedColumns

End Sub

Notes:

– Counts the number of used (cells with data or formatted) columns in the worksheet, starting from the first used column till the last used column. Does not give column no of last used column.

– Hidden columns are also counted.

– In case of a blank worksheet it will return the value 1.

————————————————————————————————————-

UsedRange property to find the first used row number in a worksheet

Sub FirstUsedRow_UsedRange_1()

‘UsedRange property to find the first used row number in a worksheet

Dim firstRow As Long

firstRow = ActiveSheet.UsedRange.Cells(1).Row

MsgBox firstRow

End Sub

Sub FirstUsedRow_UsedRange_2()

‘UsedRange property to find the first used row number in a worksheet

Dim firstRow As Long

firstRow = ActiveSheet.UsedRange.Row

MsgBox firstRow

End Sub

Notes:

– This determines the first used (cells with data or formatted) row in a worksheet. In case of a blank worksheet it will return the value 1.

– Hidden rows are also counted.

————————————————————————————————————-

UsedRange property to find the first used column number in a worksheet

Sub FirstUsedColumn_UsedRange_1()

‘UsedRange property to find the first used column number in a worksheet

Dim firstColumn As Integer

firstColumn = ActiveSheet.UsedRange.Cells(1).Column

MsgBox firstColumn

End Sub

Sub FirstUsedColumn_UsedRange_2()

‘UsedRange property to find the first used column number in a worksheet

Dim firstColumn As Integer

firstColumn = ActiveSheet.UsedRange.Column

MsgBox firstColumn

End Sub

Notes:

– This determines the first used (cells with data or formatted) column in a worksheet. In case of a blank worksheet it will return the value 1.

– Hidden columns are also counted.

————————————————————————————————————-

Use End(xlDown) to determine Last Row with Data, at the End of a Block in a column

Sub LastRowWithData_xlDown()

‘End(xlDown) method to determine Last Row with Data, at the End of a Block in a column (column D)

Dim lastRow As Long

lastRow = ActiveSheet.Range(“D2”).End(xlDown).Row

MsgBox lastRow

End Sub

Notes:

xlDown looks down from the specified range in formula, for the first empty cell it finds (in the column) and returns the last used row number before that first empty cell. If the specified range cell is empty OR ELSE if the specified range cell has data but cells immediately after this are empty, in both these cases xlDown will return the first used row AFTER the empty cell(s). Effectively, xlDown returns the last used row in a worksheet, only if it is AFTER the specified range.

If the column is empty or if there is no used row/cell after the specified range, it returns the last row in Excel viz. row 65,536 in Excel 2003. In this scenario you can use the code:

lastRow = ActiveSheet.Range(“D2”).End(xlDown).End(xlDown).End(xlUp).Row.

This formula will return the value 1 for an empty column, or otherwise (if all cells/rows after the specified range are empty) return the actual last used row in the full column.

xlDown ignores hidden rows which might have data in them. It does not count a formatted cell but with no data.

End(xlUp) is a commonly used method to determine the last used row right till the end of a specified column, irrespective of blanks. End(xlDown) gets the last cell before blank in a column, whereas End(xlToRight) gets the last cell before blank in a row.

————————————————————————————————————-

Use End(xlToRight) to determine Last Column with Data, at the End of a Block in a row

Sub LastColumnWithData_xlDown()

‘End(xlToRight) method to determine Last Column with Data, at the End of a Block in a row (row 4)

Dim lastColumn As Integer

lastColumn = ActiveSheet.Range(“C4”).End(xlToRight).Column

MsgBox lastColumn

End Sub

Notes:

All explanations as in “End(xlDown) method to determine Last Row with Data, at the End of a Block in a column”, are applicable.

Examples of using Range.End Property, for selecting a particular row or column – refer Image 1:

Sub EndProperty()
‘using the End property – refer Image 1.

Dim ws As Worksheet

Set ws = Worksheets(“Sheet1”)

ws.activate

‘selects cell C12 (Helen):

Range(“C5”).End(xlDown).Select

‘selects cell C17 (55) – C12 is the last cell with data in a block, and in this case it selects the next cell with data which is C17:

Range(“C12”).End(xlDown).Select

‘selects cell C18 (66):

Range(“C17”).End(xlDown).Select

‘selects cell C17 (55) – C14 is a blank cell, and in this case it selects the next cell with data:

Range(“C14”).End(xlDown).Select

‘selects the last worksheet row if the column is blank – cell F1048576 in this case (Excel 2007 has 1048576 rows):

Range(“F1”).End(xlDown).Select

‘selects cell E7 (7):

Range(“C7”).End(xlToRight).Select

‘selects cell G7 (22):

Range(“E7”).End(xlToRight).Select

‘selects cell XFD7, which is the last column in row 7, because cell I7 is the last cell with data in this row:

Range(“I7”).End(xlToRight).Select

‘selects cell I7 (26):

Range(“I14”).End(xlUp).Select

‘selects cell E6 (Jim):

Range(“E18”).End(xlUp).Select

‘selects range C5:C12 – from cell C5 to bottom end of the range at cell C12:

Range(“C5”, Range(“C5”).End(xlDown)).Select

End Sub

————————————————————————————————————-

FIND method to determine Last Row with Data, in a worksheet

Related link: Find method in excel VBA.

Sub LastUsedRow_Find()

‘FIND method to determine Last Row with Data, in a worksheet

Dim lastRow As Long

Dim rng As Range

Set rng = ActiveSheet.Cells

lastRow = rng.Find(What:=“*”, After:=rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

MsgBox lastRow

End Sub

Notes:

– Returns last used row (with data) number in a worksheet. In case of a blank worksheet it will give a run-time error.

– Hidden rows are also counted.

– It does not count a formatted cell with no data.

Find Method in Excel VBA:

To search for a specific item or value in a range, use the Find Method which returns the Range, ie. the first cell, where the item or value is found. If no match (ie. matching cell) is found, it returns Nothing.

SearchDirection:

You can specify xlNext or xlPrevious – SearchDirection xlNext searches downwards (ie. the next matching value) and xlPrevious looks upwards or backwards (ie. the previous matching value) in the search range. Default value is xlNext. If you specify After:=Range(“A13”) wherein the search range is Range(“A1:A20”) and set your SearchDirection:=xlNext, then the find function will begin searching from Range(“A14”) till Range(“A20”) and then search from Range(“A1”) till Range(“A13”). If you specify After:=Range(“A1”) wherein the search range is Range(“A1:A20”) and set your SearchDirection:=xlPrevious, then the find function will begin searching from Range(“A20”) till Range(“A1”) – this is how we have searched in the above code.

————————————————————————————————————-

FIND method to determine Last column with Data, in a worksheet

Sub LastUsedColumn_Find()

‘FIND method to determine Last column with Data, in a worksheet

Dim lastColumn As Integer

Dim rng As Range

Set rng = ActiveSheet.Cells

lastColumn = rng.Find(What:=“*”, After:=rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column

MsgBox lastColumn

End Sub

Notes:

– Returns last used column (with data) number in a worksheet. In case of a blank worksheet it will give a run-time error.

– Hidden columns are also counted.

– It does not count a formatted cell with no data.

————————————————————————————————————-

SpecialCells method to find Last Used Row in worksheet

Sub LastUsedRow_SpecialCells_1()

‘SpecialCells method to find Last Used Row in worksheet – using xlCellTypeLastCell constant in the method (xlCellTypeLastCell uses the UsedRange to find the last cell).

Dim lastRow As Long

lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

MsgBox lastRow

End Sub

Sub LastUsedRow_SpecialCells_2()

‘SpecialCells method to find Last Used Row in worksheet – using xlCellTypeLastCell constant in the method (xlCellTypeLastCell uses the UsedRange to find the last cell).

Dim lastRow As Long

lastRow = ActiveSheet.Range(“A1”).SpecialCells(xlCellTypeLastCell).Row

MsgBox lastRow

End Sub

Notes:

– This method determines the last used (cells with data or formatted) row in a worksheet. In case of a blank worksheet it will return the value 1.

– If data is deleted in the worksheet (ie. cells have been used earlier but contents cleared now), or if rows are deleted, this method will remember and retain what you had as the last cell and will re-set only when the file is Saved or in some cases when the file is saved, closed and reopened.

– This method also counts formatted cells with no data, viz. if you apply Date format to a cell. In this case clearing the content/format might not be enough to re-set, you will have to delete the particular row.

– This method ignores hidden rows and is usually unpredictable in case hidden rows are present.

– Due to the above reasons, this method might include seemingly visible blank cells and is generally regarded as unreliable in VBA.

Range.SpecialCells Method:

For referring to cells meeting a specified criteria, use the Range.SpecialCells Method. Syntax: RangeObject.SpecialCells(Type, Value). The Type argument specifies the Type of cells as per the XlCellType constants, to be returned. It is mandatory to specify this argument. The Value argument is optional, and it specifies values (more than one value can be also specified by adding them) as per the XlSpecialCellsValue constants, in case xlCellTypeConstants or xlCellTypeFormulas is specified in the Type argument. Not specifying the Value argument will default to include all values of Constants or Formulas in case of xlCellTypeConstants or xlCellTypeFormulas respectively. Using this method will return a Range object, comprising of cells matching the Type & Value arguments specified.

XlCellType constants: xlCellTypeAllFormatConditions (value -4172, refers to all cells with conditional formatting); xlCellTypeAllValidation (value -4174, refers to cells containing a validation); xlCellTypeBlanks (value 4, refers to blank or empty cells); xlCellTypeComments (value -4144, refers to cells with comments); xlCellTypeConstants (value 2, refers to cells containing constants); xlCellTypeFormulas (value -4123, refers to cells with formulas); xlCellTypeLastCell (value 11, refers to the last cell in the used range); xlCellTypeSameFormatConditions (value -4173, refers to cells with same format); xlCellTypeSameValidation (value -4175, refers to cells with same validation); xlCellTypeVisible (value 12, refers to all cells which are visible).

XlSpecialCellsValue constants: xlErrors (value 16); xlLogical (value 4); xlNumbers (value 1); xlTextValues (value 2).

————————————————————————————————————-

SpecialCells method to find Last Used Column in worksheet

Sub LastUsedColumn_SpecialCells_1()

‘SpecialCells method to find Last Used Column in worksheet – using xlCellTypeLastCell constant in the method (xlCellTypeLastCell uses the UsedRange to find the last cell).

Dim lastColumn As Integer

lastColumn = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

MsgBox lastColumn

End Sub

Sub LastUsedColumn_SpecialCells_2()

‘SpecialCells method to find Last Used Column in worksheet – using xlCellTypeLastCell constant in the method (xlCellTypeLastCell uses the UsedRange to find the last cell).

Dim lastColumn As Integer

lastColumn = ActiveSheet.Range(“A1”).SpecialCells(xlCellTypeLastCell).Column

MsgBox lastColumn

End Sub

Notes:

– This method determines the last used (cells with data or formatted) column in a worksheet. In case of a blank worksheet it will return the value 1.

– If data is deleted in the worksheet (ie. cells have been used earlier but contents cleared now), or if columns are deleted, this method will remember and retain what you had as the last cell and will re-set only when the file is Saved or in some cases when the file is saved, closed and reopened.

– This method ignores hidden columns and is usually unpredictable in case hidden columns are present.

– Due to the above reasons, this method might include seemingly visible blank cells and is generally regarded as unreliable in VBA.

VBA codes to determine Last Used Row & Last Used Column in Excel

VBA codes to determine Last Used Row & Last Used Column in Excel

VBA codes to determine Last Used Row & Last Used Column in Excel

VBA TUTORIAL LOGIN

Cond Statements in VBA Test

Text Strings in VBA

Custom Number Formats

Excel VBA Built-in Events

ActiveX & Form Controls

Application.OnTime Method

MsgBox & InputBox in VBA

Leave a Reply Cancel reply