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 : http://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 : http://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 : http://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.
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!
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
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!!
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!
Reblogged this on SutoCom Solutions.
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.
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.
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()
End Sub
Private Sub olInboxItems_ItemAdd(ByVal Item As Object)
End Sub
But I I face with an error that says: “ByRef argument type mismatch”
whats wrong?
thanks for your help
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
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! 😀
Hi again.
Remove the on error resume next statement and check where your error is actually happening.
Also, I think this issue has little to do with this post, and I’d prefer if we took this conversation elsewhere.
Write to me at strugglingtoexcel@outlook.com
Pingback: copy data from outlook email attachments to a local file
What’s up to all, since I am genuinely eager of reading this website’s post to
be updated on a regular basis. It contains good information.
Thank you for your kind words. Much appreciated.
Pingback: Error trapping for dynamic array
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
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.
That is a limitation I did not realize existed. Let me test it out and get back to you.
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.
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/
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.
You can use the special cells property to fetch only visible cells after getting the actual used range using my function.
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?
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.
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.
You might want to check out my post on finding out used areas. Have you tried it out?
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!
I just realized what you meant. All you have to do, is use my Last Cell in Sheet function to get the last cell. Then used the Range function: Range(YourFirstCellRange,MyLastCellFunction) and you will be good to go. Sorry I took a year to respond 😀
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 . . .
No Problem! Is everything solved now? Do you need any more help?
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)?
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?