Excel VBA usedRange Property and reset usedRange

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

You may also want to read:

Excel VBA custom Function last row and last column

Excel delete blank rows and blank cells before import into Access

Excel VBA 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 ever 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

usedRange

Reset UsedRange

UsedRange can be reset using the following Procedure.

Public Sub reset_usedrange()
    a = ActiveSheet.UsedRange.Rows.Count
End Sub

The above procedure work under normal circumstances, but it does not work in some cases where the data source was downloaded from other system. In that case you should use the below procedure to clear all empty cells and then delete the row.

Public Sub delete_empty_row()
    Application.ScreenUpdating = False

    For Each usedrng In ActiveSheet.UsedRange
        If usedrng.MergeCells = True Then
            If usedrng.Value = "" Then
                usedrng.Value = ""
            End If
        Else
            If usedrng.Value = "" Then
                usedrng.ClearContents
            End If
        End If
    Next
    
    ActiveSheet.UsedRange
    usedRangeLastColNum = ActiveSheet.UsedRange.Columns.Count
    usedrangelastrow = ActiveSheet.UsedRange.Rows.Count
    
    For r = usedrangelastrow To 1 Step -1
        If Application.WorksheetFunction.CountA(Cells(r, usedRangeLastColNum).EntireRow) <> 0 Then
            Exit For
        Else
            Cells(r, usedRangeLastColNum).EntireRow.Delete
        End If
    Next r
    
    For c = usedRangeLastColNum To 1 Step -1
        If Application.WorksheetFunction.CountA(Cells(1, c).EntireColumn) <> 0 Then
            Exit For
        Else
            Cells(1, c).EntireColumn.Delete
        End If
    Next c
    
    ActiveSheet.UsedRange
    Application.ScreenUpdating = True
End Sub

Find the last used row and column

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

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

To convert column number to column letter

usedRangeLastColNm= Split(Cells(1,Activesheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column).Address, "$")(1)

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

Afterwards you can use split function to get  the starting or ending Range.

Click here to see how to use Split Function.

Outbound References

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

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

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

last row

FormulaResultExplanation
=wColLastRow(“Sheet1″,”B”)4Column B last row number
=wRowLastColNum(“Sheet1”,1)3Row 1 last column number
=wRowLastColNm(“Sheet1”,1)CRow 1 last column name
=wColLastRowAdd(“Sheet1″,”A”)$A$3Cell address of column A last row
=wRowLastColAdd(“Sheet1”,2)$C$2Cell address of row 2 last column
=wColLastRowVal(“Sheet1″,”C”)data3Cell value of column C last row
=wRowLastColVal(“Sheet1”,4)data5Cell value of row 4 last column

Outbound References

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