Get the Actual Used Range in a Spreadsheet


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.

Actual Used Range

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 ‘Actual Used Range.xlsm‘ from Dropbox 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.

Advertisements

31 thoughts on “Get the Actual Used Range in a Spreadsheet

  1. Ejaz, I keep running into situations where data has multiple rows of header information that I don’t want included in the actual range. Instead, I’m curious if it would be possible to exchange the boolean in FirstCellInSheet for an actual range value. That way, with an search function I can find the true header row based on a specific string value and feed that row as the FirstCellInSheet.

    Like

      • I just saw that post today for the first time. I’m kind of on the verge of seeing how that could work, but I’ll have to trial/error to see if I can implement your suggestion (thinking kind of fuzzy at the moment). I guess my challenge is that I’m going the other way than the UsedAreas post — instead of formatting all the areas together, I was hoping to isolate/manipulate only one and exclude all others by passing the row where the true header begins (ignoring all the title/date/author fluff at the top) to the FirstCellInSheet function.

        The other way I can think of is to get the full ActualUsedRange and then manipulate/reset the range by injecting the first row with something like:
        TruncRange = Range(FirstCellInSheet .Column & rngTrueHeader.Row & “:” & LastCellInSheet), where rngTrueHeader.Row is the xth row of the ActualUsedRange, excluding the top fluff.

        Do you think that’s a viable approach?? Probably kind of hard to envision without an actual example 😐

        Thanks again for all your great work!

        Like

      • I’m just now realizing that your comment on 28 Aug re: using hidden blank rows can work towards accomplishing my current conundrum. I’m slow sometimes . . .

        Like

          • I really like your UsedAreas function!! I folded in your ActualUsed instead of UsedRange too 😉 This function is in almost all of my code!!

            I was curious how I can isolate and work with only the largest area (analogous to using only the Data Body Range in a table)?

            Liked by 1 person

            • I am glad you like it.

              I suppose you could loop through all the areas using the area collection of the range object, and use the count property to keep track of the address of the area that had the largest number of cells. Does that help?

              Like

  2. Ejaz,
    Great stuff, as usual. I had a question about ActualUsedRange function. The first cell on my sheet is merged, so when I run your function (ActualUsedRange), I get A1:A1 as the output. Is there a workaround to keep the merged cell (title block) and still have an accurate output for the actual used range?

    Thanks much.

    Liked by 1 person

      • In experimenting, I found that this issue only occurs when A1 is part of the merged block; if A1 is unmerged (and the merged title block is below/aside it), it works beautifully.

        I know merged cells are taboo (for good reason), but inserting a blank row above the merged header is a perfectly acceptable workaround to me.

        Liked by 1 person

        • I totally agree, I always leave blank rows and columns around every “table”, including the headers.

          Aside from looking aesthetically pleasing, it helps you use the current region properties and area properties effectively.

          Even if the structure of a report does not need a blank row in-between, I’d add it anyway, and hide it. I use Boolean markers to specify which rows to show/hide in my excel models. I recommend you read this article if you are interested in quickly hiding/un-hiding rows and columns in your model without using custom views: https://strugglingtoexcel.com/2014/04/25/hide-rows-columns-excel-vba/

          Like

          • You, sir, are a rock star!! Thanks so much for the great ideas.

            One last question on the topic of used ranges – is there a way to limit the used range to the visible cells only (a switch on .Find or something)??

            Thanks again.

            Liked by 1 person

              • When I attempt to redefine lastrow/lastcolumn using .SpecialCells(xlCellTypeVisible), I get A1. Is this because I now have a blank row (row 1) as a workaround to the issue with the merge title block and the last visible cell is A1 (even though it’s actually blank)??

                I’m redefining LastColumn = TempRange.SpecialCells(xlCellTypeVisible).Column- am I doing it wrong?

                Like

                • You don’t have to redifine those functions. Just get the actual used range first. Put it in a range object. Then use the special cells property of that range object to get only visible cells.

                  Like

  3. Sub SelectActualUsedRange()
    Dim firstcell As Range, lastcell As Range
    Set lastcell = Cells(Cells.Find(What:=”“, SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
    Cells.Find(What:=”
    “, SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Column)
    Set firstcell = Cells(Cells.Find(What:=”“, After:=lastcell, SearchOrder:=xlRows, _
    SearchDirection:=xlNext, LookIn:=xlValues).Row, _
    Cells.Find(What:=”
    “, After:=lastcell, SearchOrder:=xlByColumns, _
    SearchDirection:=xlNext, LookIn:=xlValues).Column)
    Range(firstcell, lastcell).Select
    End Sub

    Like

  4. Pingback: Error trapping for dynamic array

  5. Pingback: copy data from outlook email attachments to a local file

  6. Hi
    Really thank you for your helpful topic.

    but I dont know why It doesnt work for me!

    I’m writing a vba code in outlook to open the excel attachments of the emails with a specific subject, and copy all data on it, then paste it in my excel file on my directory. the cod is here:

    Option Explicit
    Dim WithEvents OLInboxItems As Items

    Private Sub Application_Startup()

    Dim OLNS As Outlook.NameSpace
    
    Set OLNS = Application.GetNamespace("MAPI")
    Set OLInboxItems = OLNS.GetDefaultFolder(olFolderInbox).Items
    

    End Sub

    Private Sub olInboxItems_ItemAdd(ByVal Item As Object)

    On Error Resume Next
    
    Dim OLMailItem As MailItem
    Dim AttPath, AttName, UpFilePath
    
    If TypeOf Item Is MailItem Then
        Set OLMailItem = Item
    
        If OLMailItem.Attachments.Count > 0 _
          And OLMailItem.Subject = "AHOrdUpdate" Then
    
            AttName = OLMailItem.Attachments.Item(1).FileName
            AttPath = "D:\Projects\excel\TAC\Factory\TACDataManagement\Orders\AHOrders\Updates\" & AttName
            UpFilePath = "D:\Projects\excel\TAC\Factory\TAC Program\FDB.xlsm"
            OLMailItem.Attachments.Item(1).SaveAsFile AttPath
    
        End If
    
        Dim xlApp, AttWB, FDBWB As Workbook
        Dim AttWS, FDBWS As Worksheet
        Dim AttEOC, AttEOR, FDBEOC, FDBWBCheck As Integer
        Dim CpyRng As Range
    
        Set xlApp = CreateObject("Excel.Application")
    
        With xlApp
            .Visible = False
            .EnableEvents = False
        End With
    
        Set AttWB = Workbooks.Open(AttPath)
        'Set AttWS = AttWB.Sheets("sheet1")
    
        If FDBWB Is Nothing Then
            Set FDBWB = Workbooks.Open(UpFilePath)
            FDBWBCheck = 1
        End If
    
        Set FDBWS = FDBWB.Sheets("sheet1")
    
        'AttWS.Activate
    
        AttWB.Application.CutCopyMode = True
    
        CpyRng = ActualUsedRange(AttWS)
    
        CpyRng.Copy
    
        FDBWB.Cells(EndOfColumn(FDBWS, "A"), "A").PasteSpecial
    
        AttWB.Application.CutCopyMode = False
    
        AttWB.Close False
    
        If FDBWBCheck = 1 Then
            FDBWB.Close True
        End If
    
        Kill AttPath
    
    End If
    Set Item = Nothing
    Set OLMailItem = Nothing
    

    End Sub

    But I I face with an error that says: “ByRef argument type mismatch”

    whats wrong?

    thanks for your help

    Like

    • Hi Samira,

      I am glad that you found my post helpful. I have not used VBA for outlook much, but I think what is creating this error.

      You should try using Set CpyRng = ActualUsedRange(AttWS). Since CpyRange is an object, not a variable, the Set statement is required.

      I would also like to go ahead and let you know that my function is not required in this context. This function is useful if your sheet is dynamic, and when you can not afford to save the file to update the UsedRange. Considering the fact that you are working with a brand new file, you can just use the Set CpyRange = AttWS.UsedRange.

      Try this and let me know if this solves your problem. Otherwise feel free to write back to me here, or at strugglingtoexcel@outlook.com

      Like

      • Hi again and thanks helping me

        I did whatever you told me in last post. But nothing changed!

        you know my variables get no value! when I run this code step by step I see that the workbook and worksheet variables returns “Nothing”

        I Hope I can tell you what I mean! My English is not very good! 😀

        Like

  7. Ejaz Ahmed, this is a very useful set of functions. I do have a question. Is there a way to modify the code to exclude one or more rows at top of a range or column(s) at left of data range? For example, I often reserve row 1 for command or toggle buttons that run sheet-specific code. I then leave a blank row and on following row place data headers. In the demo I was unable to figure out if these rows above the header row can be excluded in determining the dimensions of the data range.

    Any help would be greatly appreciated.

    Thank you.

    Like

    • You can always use this function first, and then use the offset method.

      Dim MyRange as Range
      Set MyRange = ActualUsedRange(sheet)
      Set MyRange = MyRange.Offset(x).Resize(MyRange.Rows.Count – x)

      Where x is the number of rows you want to exclude from the top. Excuse my tardy reply, WordPress tagged your comment as spam for some reason.

      Like

  8. Hi, in your ActualUsedRange function, what is modActualUsedRange?
    is this a typo– and is actually just supposed to be a recursive call?

    If so, i get an “Object variable or With block variable not set” error upon changing it (from modActualUsedRange to ActualUsedRange).

    I’d be really happy if you could clear this up for me!
    I really want to use your function, i’ve been stuck on finding the actual used range of some report worksheets for a LONG TIME.

    Thanks!

    Liked by 1 person

    • Hi Gina,

      I am sorry I confused you a little. modActualUsedRange is just the module I stored the functions in. I really should have taken that out before I posted the code.

      Thanks for pointing that out to me. Just get rid of it, and the code should work just fine.

      Regards,
      Ejaz Ahmed

      Like

      • It works perfectly!!!!!!!!!!!!!!!
        this is exactly what i needed.
        i noticed you posted this just yesterday, I feel very lucky that you did!

        I kept all your information in my code so the credit is all yours!
        THANKS!!

        Like

        • Hi Gina, I am glad that my code helped. I flicked it from another article myself, but added my own touches to make that code a tad more useful.

          I didn’t post anything for over a month, so got some stuff done over the weekend.

          I’d be grateful if you would like my blog’s facebook page (link at the top of this page), and invite a few friends over. This blog might help them too!

          Liked by 1 person

If you liked it, let me know. If you didn't make sure you let me know!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s