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.

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 has a Worksheet Property called UsedRange, which defines the area Range bounded by last used column and last used row. Used Cell is defined as Cell containing formula, formatting, value.

The picture below highlights the area of UsedRange

To find the last row number, use the below code

usedRangeLastRow = Activesheet.UsedRange.Rows.Count

To find the last column number, use the below code

usedRangeLastColNum = Activesheet.UsedRange.Columns.Count

 

It is not recommended to use UsedRange to find the last column and row for the following reasons:

– Formatted Cell without value is considered as used

– Even if you delete the format of a formatted Cell, the Cell is still considered as used

– Because the above formula uses Count Method, data must start in Cell A1

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

Excel VBA custom Function last row and last column