#usedrange

New Post has been published on http://access-excel.tips/excel-vba-usedrange-property/

Excel VBA usedRange Property

This tutorial explains how to use Excel VBA usedRange Property to find the last used row and column number.

Excel VBA UsedRange Property

Excel VBA UsedRange is a worksheet Property, it returns 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

UsedRange is best to find the data Range where some data in the last rows are blank. In our example, even though Cell C4 is blank, it still includes C4 due to format in E4 and data in B4.

Find the last used row and column

A popular use of UsedRange Property to find the last used row and column.

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

However, 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

If you want to find the last row and column, use End Property instead. Click here to read more.

Find the address of usedRange

Address Property returns the absolute address of a Range. In the above example, the below code will return $B$1:$E$4

Activesheet.UsedRange.Address

Outbound References

New Post has been published on http://access-excel.tips/last-row-and-last-column/

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