Excel VBA custom Function last row and last column

This tutorial explains how to create a custom VBA Function to find the last row and last column number in Excel worksheet.

You may also want to read:

Excel find the last row using Worksheet formula

Excel VBA custom Function last row and last column

In Excel VBA, we always need to loop through each Cell to perform some actions.

For Range that you already know, you can easily use For…Each Loop to loop through each Cell for a specified Range as below

For Each rng in Range("A1:C10")
   'Your code
Next

However, you may not know the last row or last column. For example, the number of rows may change each time the data sheet has new data.

You can use OFFSET Function to set a dynamic Range, but it is easier to to find the last row and column in VBA, and then through through each row and column as below

For r = 1 to lastrow
   For c = 1 to lastCol
     'Your Code
   Next c
Next r

Find Excel last row and last column number – using UsedRange Property

Excel VBA UsedRange is a worksheet Property, it returns the area Range bounded by first used cell and last used cell. “Used Cell” is defined as Cell containing formula, formatting, value that has even been used, even though the value was deleted. In Excel worksheet, we can press Excel shortcut key CTRL+END to select the last used cell.

The picture below highlights the area of UsedRange

To find the last row number, use the below code

usedRangeLastRow = Activesheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

To find the last column number, use the below code

usedRangeLastColNum = Activesheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column

Instead of using usedRange, below are alternatives that return the same result

usedRangeLastRow = Activesheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row

usedRangeLastRow = Activesheet.Cells.SpecialCells(xlCellTypeLastCell).Row

Find Excel last row and last column number – using End Property

End Property is the standard way to find the last row and last column. End Property is same as the action of pressing Ctrl+direction arrow on your keyboard, it looks for the Cell that contains a value or formula. Cell that does not have any value but contains formula is not regarded as last Cell.

The below custom Function looks for the last row number (1,2,3,4…) in specific column

Public Function wColLastRow(worksheetNm As String, colNm As String) As Integer
    wColLastRow = Worksheets(worksheetNm).Range(colNm & Rows.Count).End(xlUp).row
End Function

The below custom Function looks for the last column number (1,2,3,4…) in specific row

Public Function wRowLastColNum(worksheetNm As String, rowNum) As Integer
    wRowLastColNum = Worksheets(worksheetNm).Range("IV" & rowNum).End(xlToLeft).Column
End Function

Find Excel last column name (A,B,C…)

The below custom Function looks for the last column name  of specific row

Public Function wRowLastColNm(worksheetNm As String, rowNum) As String
    wRowLastColNm = Split(Cells(1, Worksheets(worksheetNm).Range("IV" & rowNum).End(xlToLeft).Column).Address, "$")(1)
End Function

Find Excel last row and last column Range Address ($A$1,$B$1,$C$1…)

The below custom Function looks for the Range absolute Address of last row in specific column

Public Function wColLastRowAdd(worksheetNm As String, colNm As String) As String
    wColLastRowAdd = Worksheets(worksheetNm).Range(colNm & Rows.Count).End(xlUp).Address
End Function

The below custom Function looks for the Range absolute Address of last column  in specific row

Public Function wRowLastColAdd(worksheetNm As String, rowNum) As String
    wRowLastColAdd = Worksheets(worksheetNm).Range("IV" & rowNum).End(xlToLeft).Address
End Function

Find Excel last row and last column Range Value

The below custom Function looks for the Range value of last row in specific column

Public Function wColLastRowVal(worksheetNm As String, colNm As String)
    wColLastRowVal = Worksheets(worksheetNm).Range(colNm & Rows.Count).End(xlUp).Value
End Function

The below custom Function looks for the Range value of last column  in specific row

Public Function wRowLastColVal(worksheetNm As String, rowNum)
    wRowLastColVal = Worksheets(worksheetNm).Range("IV" & rowNum).End(xlToLeft).Value
End Function

Example – Find Excel last row and last column

Formula	Result	Explanation
=wColLastRow(“Sheet1″,”B”)	4	Column B last row number
=wRowLastColNum(“Sheet1”,1)	3	Row 1 last column number
=wRowLastColNm(“Sheet1”,1)	C	Row 1 last column name
=wColLastRowAdd(“Sheet1″,”A”)	$A$3	Cell address of column A last row
=wRowLastColAdd(“Sheet1”,2)	$C$2	Cell address of row 2 last column
=wColLastRowVal(“Sheet1″,”C”)	data3	Cell value of column C last row
=wRowLastColVal(“Sheet1”,4)	data5	Cell value of row 4 last column

Outbound References

https://msdn.microsoft.com/en-us/library/office/aa215513%28v=office.11%29.aspx