Excel VBA
VBA codes to determine Last Used Row & Last Used Column in Excel
/ 16
Details
- Details
- Category: Excel VBA
VBA Codes - Find last used row in a worksheet or in a specific column; Find last used column in a worksheet or in a specific row
Related Links: Find last used row number & cell address with excel functions.
End(xlUp) method to determine Last Row with Data, in one column
Sub LastRowWithData_xlUp_1()
Dim lastRow As Long
lastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
MsgBox lastRow
End Sub
Sub LastRowWithData_xlUp_2()
Dim lastRow As Long
lastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
MsgBox lastRow
End Sub
Returns last used row (with data) number in a specific column (column "B" here). 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) - it will not consider this row.
If the last row with data is hidden, this row is ignored and not counted.
End(xlUp) is the most commonly used method to determine the last used row or column, with data.
-------------------------------------------------------------------------------------------------------------
End(xlUp) method to determine Last Column with Data, in one row
Sub LastColumnWithData_xlToLeft()
Dim lastColumn As Integer
lastColumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
MsgBox lastColumn
End Sub
Returns last used column (with data) number in a specific row (row 2 in this formula). 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 column (viz. column 256 in Excel 2003) - it will not consider this column
If the last column with data is hidden, this column is ignored and not counted.
End(xlUp) is the most commonly used method to determine the last used row or column, with data.
-------------------------------------------------------------------------------------------------------------
UsedRange method to find the last used row number in a worksheet
Sub LastUsedRow_UsedRange_1()
Dim lastRow As Long
lastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
MsgBox lastRow
End Sub
Sub LastUsedRow_UsedRange_2()
Dim lastRow As Long
lastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
MsgBox lastRow
End Sub
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.
Hidden rows are also counted.
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.
-------------------------------------------------------------------------------------------------------------
UsedRange method to find the last used column number in a worksheet
Sub LastUsedColumn_UsedRange_1()
Dim lastColumn As Integer
lastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
MsgBox lastColumn
End Sub
Sub LastUsedColumn_UsedRange_2()
Dim lastColumn As Integer
lastColumn = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
MsgBox lastColumn
End Sub
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.
Hidden columns are also counted.
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.
-------------------------------------------------------------------------------------------------------------
UsedRange method to find number of used rows in a worksheet
Sub UsedRows_UsedRange()
Dim usedRows As Long
usedRows = ActiveSheet.UsedRange.Rows.Count
MsgBox usedRows
End Sub
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.
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.
In case of a blank worksheet it will return the value 1.
-------------------------------------------------------------------------------------------------------------
UsedRange method to find number of used columns in a worksheet
Sub UsedColumns_UsedRange()
Dim usedColumns As Integer
usedColumns = ActiveSheet.UsedRange.Columns.Count
MsgBox usedColumns
End Sub
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.
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.
In case of a blank worksheet it will return the value 1.
-------------------------------------------------------------------------------------------------------------
UsedRange method to find the first used row number in a worksheet
Sub FirstUsedRow_UsedRange_1()
Dim firstRow As Long
firstRow = ActiveSheet.UsedRange.Cells(1).Row
MsgBox firstRow
End Sub
Sub FirstUsedRow_UsedRange_2()
Dim firstRow As Long
firstRow = ActiveSheet.UsedRange.Row
MsgBox firstRow
End Sub
This method 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.
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.
-------------------------------------------------------------------------------------------------------------
UsedRange method to find the first used column number in a worksheet
Sub FirstUsedColumn_UsedRange_1()
Dim firstColumn As Integer
firstColumn = ActiveSheet.UsedRange.Cells(1).Column
MsgBox firstColumn
End Sub
Sub FirstUsedColumn_UsedRange_2()
Dim firstColumn As Integer
firstColumn = ActiveSheet.UsedRange.Column
MsgBox firstColumn
End Sub
This method 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.
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.
-------------------------------------------------------------------------------------------------------------
End(xlDown) method to determine Last Row with Data, in one column
Sub LastRowWithData_xlDown()
Dim lastRow As Long
lastRow = ActiveSheet.Range("D2").End(xlDown).Row
MsgBox lastRow
End Sub
xlDown looks down from the specified range in formula, for the first empty cell it finds and returns the last used row 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 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.
Use xlUp to determine the last used row right till the end of a specified column, irrespective of blanks.
-------------------------------------------------------------------------------------------------------------
End(xlToRight) method to determine Last Column with Data, in one column
Sub LastColumnWithData_xlDown()
Dim lastColumn As Integer
lastColumn = ActiveSheet.Range("C4").End(xlToRight).Column
MsgBox lastColumn
End Sub
All explanations as in "End(xlDown) method to determine Last Row with Data, in one column", are applicable.
-------------------------------------------------------------------------------------------------------------
FIND method to determine Last Row with Data, in a worksheet
Related link: Find method in excel VBA.
Sub LastUsedRow_Find()
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
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 to determine Last column with Data, in a worksheet
Sub LastUsedColumn_Find()
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
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()
Dim lastRow As Long
lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
MsgBox lastRow
End Sub
Sub LastUsedRow_SpecialCells_2()
Dim lastRow As Long
lastRow = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row
MsgBox lastRow
End Sub
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 is generally regarded as unreliable in VBA.
-------------------------------------------------------------------------------------------------------------
SpecialCells method to find Last Used Column in worksheet
Sub LastUsedColumn_SpecialCells_1()
Dim lastColumn As Integer
lastColumn = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
MsgBox lastColumn
End Sub
Sub LastUsedColumn_SpecialCells_2()
Dim lastColumn As Integer
lastColumn = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column
MsgBox lastColumn
End Sub
This method determines the last used column (cells with data or formatted) 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 columns and is usually unpredictable in case hidden columns are present.
Due to the above reasons, this method is generally regarded as unreliable in VBA.