In one of my posts, I wrote about the UsedRange property of the Worksheet object. I use it in almost all of my spreadsheet applications. Excel keeps track of the last cell you used during your current session, and uses it to determine the used range of a sheet. The last cell is the one you get to, when you press Ctrl+End.

You can get to the last cell in a sheet using the SpecialCells method of the Range object also:

Sub SelectLastCell() Dim WhichSheet As Worksheet Dim TempRange As Range Set WhichSheet = Application.ActiveSheet Set TempRange = WhichSheet.Cells.SpecialCells(xlCellTypeLastCell) TempRange.Select End Sub

There is one tiny problem with using the UsedRange property. Say you had content in your sheet that you removed, or you randomly did some ad-hoc checks in your spreadsheet that you cleared by pressing the Delete button; the “Last Cell” does not get refreshed until you save the workbook. The only sure way to “update” a sheet’s UsedRange is to select all the rows and columns that contained data at some point during your session, delete it, and save the workbook. This may turn out to be a hurdle if you do not allow users the save the workbook.

Recently I read an article (actually an excerpt from a book) that used the Find method of the Range object to get the last cell that has data. I added in some extra lines to find the first cell that has data in it, and wrote a function to find the “Actual” used range in a sheet. This function accepts a worksheet object as an argument and returns the Actual Used Range. I spit it out into two more functions, one to find the first cell, and another to find the last cell in a worksheet.

Function to get the First Used Cell in a sheet:

'##################################################################### 'FirstCellInSheet : Function to Identify the First Cell in the used 'used range of a worksheet that contains data. '##################################################################### 'Author : Ejaz Ahmed 'Email : StrugglingToExcel@outlook.com 'Date : 26 May 2013 'Website : https://strugglingtoexcel.wordpress.com/ '##################################################################### Function FirstCellInSheet(ByRef WhichSheet As Worksheet) _ As Range 'Declare Function Level Variables Dim FirstRow As Long Dim FirstColumn As Long Dim TempRange As Range 'Initialize default values FirstRow = 1 FirstColumn = 1 'Get the first row that has data by setting the search direction 'to Next and search order to by-Rows If WhichSheet.Cells(1, 1).Value = vbNullString Then Set TempRange = WhichSheet.Cells.Find("*", _ , xlFormulas, xlPart, xlByRows, xlNext) If Not TempRange Is Nothing Then FirstRow = TempRange.Row 'Get the last column that has data by setting the search direction 'to Previous and search order to by-Columns Set TempRange = WhichSheet.Cells.Find("*", _ , xlFormulas, xlPart, xlByColumns, xlNext) If Not TempRange Is Nothing Then FirstColumn = TempRange.Column End If 'Return the First Cell Set FirstCellInSheet = WhichSheet.Cells.Item(FirstRow, FirstColumn) End Function

Function to get the Last Used Cell in a sheet:

'##################################################################### 'LastCellInSheet : Function to Identify the Last Cell in the used 'used range of a worksheet that contains data. '##################################################################### 'Author : Ejaz Ahmed 'Email : StrugglingToExcel@outlook.com 'Date : 26 May 2013 'Website : https://strugglingtoexcel.wordpress.com/ '##################################################################### Function LastCellInSheet(ByRef WhichSheet As Worksheet) _ As Range 'Declare Function Level Variables Dim TempRange As Range Dim LastRow As Long Dim LastColumn As Long 'Initialize default values LastRow = 1 LastColumn = 1 'Get the last row that has data by setting the search direction 'to Previous and search order to by-Rows Set TempRange = WhichSheet.Cells.Find("*", _ , xlFormulas, xlPart, xlByRows, xlPrevious) If Not TempRange Is Nothing Then LastRow = TempRange.Row 'Get the last column that has data by setting the search direction 'to Previous and search order to by-Columns Set TempRange = WhichSheet.Cells.Find("*", _ , xlFormulas, xlPart, xlByColumns, xlPrevious) If Not TempRange Is Nothing Then LastColumn = TempRange.Column 'Return the Last Cell Set LastCellInSheet = WhichSheet.Cells.Item(LastRow, LastColumn) End Function

Function to get the Actual Used Range in a sheet:

'##################################################################### 'ActualUsedRange : Function to Identify the range that actually 'contains data in the WhichSheet Worksheet Object. The Active Sheet is 'used if the WhichSheet argument is not specified. Also, you may choose 'to set the FtomTheTop argument to true, if you'd like to include the 'blank rows and columns in the beginning of the worksheet. '##################################################################### 'Author : Ejaz Ahmed 'Email : StrugglingToExcel@outlook.com 'Date : 26 May 2013 'Website : https://strugglingtoexcel.wordpress.com/ '#####################################################################\ Function ActualUsedRange(Optional ByRef WhichSheet As Worksheet, _ Optional ByVal FromTheTop As Boolean = False) _ As Range Dim LastCell As Range Dim FirstCell As Range If WhichSheet Is Nothing Then Set WhichSheet = Application.ActiveSheet End If 'Get the Last Cell Set LastCell = LastCellInSheet(WhichSheet) 'Get the First Cell If FromTheTop Then Set FirstCell = WhichSheet.Cells.Item(1, 1) Else Set FirstCell = FirstCellInSheet(WhichSheet) End If 'Return the Used Range Set ActualUsedRange = Range(FirstCell, LastCell) End Function

Remember, you may choose to set the ‘FromTheTop‘ variable to True, to include the rows and columns in the beginning of the worksheet. This comes in handy in a lot of situations, for instance: removing all blank rows and columns in a sheet.

Download

Download the file and give it a test run. This file has the three functions, and a cover macro to select the used range of the Active Sheet. Pressing Ctrl+Shift+Q executes the cover macro.