Find last row (or column) - why not just use specialcells?

I have heavily studied "how to determine the last row." There are several ways. Chip even has a page on it. What I don't get is, why the complexity? I have been unable to find a flaw in this:

Please Login or Register  to view this content.

I'll give what I believe is the answer, which you experts can respond to and ignore the rest below. Is the issue that when people want "last row" then they usually want the last row with cell contents, as opposed to where Control-end lands?

Everything below considers alternative methods of determining last row or column; not required for an expert to answer.

Some (maybe including Bob Umlas?) say the "best" way is to search for "*" using xlPrevious. Is that really necessary? Other methods involve End(xlUp) but that's column specific (and they can suffer if the very bottom row in a sheet is used). Chip says that the special cell gives the right row, but potentially wrong column. I've lived long enough to know that when I doubt Chip I always turn out to be wrong, but I've really tried and haven't gotten the incorrect column as he seems to suggest.

FYI, sht.UsedRange.Rows.Count is tempting but errs when row 1 is not used. A cute solution is
sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row
which solves that problem but it seems awfully wordy. Another suggested solution is
sht.UsedRange.Rows.Count + sht.UsedRange.Rows(1).Row - 1

So why not just sht.cells.specialcells(xlCellTypeLastCell).row ? And .column ? Is there a circumstance that breaks it? And wouldn't it be the most efficient, most "built in" approach? Remember, I'm issuing "activesheet.usedrange" first.

A few pages about it -
Chip: http://www.cpearson.com/excel/LastCell.aspx
Several methods (read the comments too): http://www.thespreadsheetguru.com/bl...lumn-using-vba
Much detailed analysis: https://www.reddit.com/r/excel/comme...ow_in_a_sheet/

Hi,

All the methods have their drawbacks. The only reason not to adopt your approach is that the usedrange is not 100% reliable and therefore Specialcells might report a higher row or column number than is strictly accurate. I personally do not think there is a "one size fits all" solution that is best in every case. It truly will depend on what you are trying to achieve.

Personally I tend to agree with Bob Ulmas about using Find with a * when dealing with multiple columns as it seems to have less issues than most other methods including either ignoring or including cells with formulas returning "" (and you should in IMHO state the setting).
For single columns I tend to use a Rows.Count xlup as long as I know that there aren't any formulas returning "".

I tend to avoid the SpecialCells method because of the inconsistentices xlnitwit has already stated + in older versions of Excel had other issues regarding the number of non-contiuous cells which some have stated also give an incorrect answer.

As xlnitwit stated they all have some issue.

EDIT: Post moved to here

http://www.excelforum.com/showthread...=1#post4487100

To prevent clutter in this Thread

A general discussion on of Last Row And UsedRange and SpecialCells(xlCellTypeLastCell) with some emphasis on the characteritics of SpecialCells(xlCellTypeLastCell), in particular emphasising on the fact that SpecialCells(xlCellTypeLastCell) uses Excels Memory of the last UsedRange, which may not necessarily be the actual current Used Range, and possible pitfalls, there from

P.s.

I just stumbled over this which I did a day or two ago for a similar Thread question - a simliar demo code

'

Please Login or Register  to view this content.

Originally Posted by Oppressed1

.......Remember, I'm issuing "activesheet.usedrange" first......

Ahh, OK..... Then it should work... ( provided the rng that SpecialCells refers to is encompassed in the current UsedRange )

check out new line 97 in that last demo code .. there I is susing .UsedRange then it will catch the correct ( current ) UsedRange apparantly in such a case.... .. makes sense

Please Login or Register  to view this content.

For information, using Activesheet.Usedrange is also not infallible in terms of resetting the actual used range. Though, again, it will suffice in the vast majority of cases.

Doc, I didn't test your code, but I think everything you say is consistent with my knowledge. One exception is that .UsedRange can be "reset" so it stops "remembering" deleted rows/cells by issuing the noninuitive command
Activesheet.usedrange
As Walkenbach discovered (unless someone else did earlier), ages ago, it achieves the same thing as saving or reopening, so Excel stops "remembering" deletions.

And I don't buy that you're a beginner. Your thought process is sound.

As to your observation for an empty sheet: it is logical that Excel's normal "domain" is a set of addresses represented in a rectangle 256 by 65536 (or version 2007 & 2010, 65536 by 1048576; 2013 is another matter). Just speaking as a programmer, if they had to handle addresses outside those ranges it would be gruesome (for one thing, those specified limits were based on CPU and memory addressing limits for a byte and an word - a technical discussion, getting off track here). So setting a "floor" row of 1 and column of 1 is likely just a logical convention, given the aforementioned "domain" grid in versions going all the way back. (Hint: calculate 2^8 and 2^16.) (Maybe subtly related, also note that much of Excel is "1" based, not "0", though some items are under the user's control.)

All - I understand that .UsedRange can extend beyond cells with contents, even recognizing formatting (e.g. if you painted ("filled") an otherwise completely unused cell yellow, .UsedRange would count it). But as to its "unreliability" - I haven't seen any real evidence. I'm not sure that that's not just an urban legend, originated from people who either didn't hit "save" (or reopen) nor did they issue the "Activesheet.usedrange" command.

One thing seems clear, .UsedRange is at least as big as the used range with data (to xlnitwit's point); and it does appear to always reproduce control-end, which I regard as the "real" last cell.

I suppose that when someone wants to append data, they want to go after the last cell that has data. Maybe its just me, but it feels like "last cell that has data" is a more restrictive definition than "last cell", or conveys a subset. I guess to each his own.

At any rate, it seems that we're flipping between SpecialCells and .UsedRange (I know I do that). They seem to be bonded, as least they appear to, and msdn says
"xlCellTypeLastCell. The last cell in the used range"

One more thing for completeness: .Used range will recognize "regular" formatting (like if you color fill a cell) but does not recognize a cell that is only conditionally formatted. That may sound surprising, but if you're doubtful check it out (quick way to observe is by hitting control end). Here I'm just describing what .UsedRange (and the SpecialCells) counts and what it doesn't, not that it's right for all applications. I still feel as a general rule that control-end is the "official" end of the world, so it looks like I'm sticking with .UsedRange and the Special cell.

Of course if there's a way to demonstrate the .UsedCells really is "unreliable" I'll be very interested.

Usedrange is indeed the same as Ctrl+End.

I can confirm that I have personally encountered worksheets where the used range cannot be reset with a simple ActiveSheet.Usedrange command. Nor will deleting extraneous rows and columns and then saving the file reset it. It is on that basis that I call it not 100% reliable, not on hearsay evidence. In such cases the simplest resolutions were: copying the data to a new worksheet, editing the XML of the workbook when possible, or using a save to HTML method.

I think we've encountered the same things. A coworker named one sheet, "the sheet from hell" where no matter how hard you tried, you could never delete a distant column like AZ. You just couldn't delete it. It wouldn't go away, even on reopening. Even so, it still seems the UsedRange is doing its job, reporting what Excel thinks is the edge of the sheet.

In retrospect I think that that, and yours, are likely corrupt workbooks. Corrupt workbooks are, depending on how they fall out, severely devastating. I can see them one day causing the end of the world. Microsoft clearly doesn't give two spits about it though, but what else is new. No s exy job promotions for addressing that little user problem

More reading for the inquisitive:

https://www.quora.com/How-do-I-use-x...astCell-in-VBA
"UsedRange and xlCellTypeLastCell are not reliable." (but no further explanation)

http://www.ozgrid.com/VBA/ExcelRanges.htm
Explains the "unreliability" but its 2 reasons don't constitute "unreliability." It lists the two items already discussed here, that Saving changes the result, and that formatting (absent cell content) changes the result.

http://www.mrexcel.com/forum/excel-q...worksheet.html
Tom Urtis says "SpecialCells LastCell, UsedRange and CurrentRegion are iffy and give bad results depending on the version, when the workbook was saved, formats, and if breaks occur in the data." Again, Tom is a smart MVP whose code I've relished, but I still think "unreliable" is the wrong term. How about it just gives different answers in different conditions.

http://stackoverflow.com/questions/1...ed-cell-in-vba
Tons o' stuff on the topic. Notably, "Some of the most common ways of finding last row which are highly unreliable and hence should never be used.
1.UsedRange
2.xlDown
3.CountA

UsedRange should NEVER be used to find the last cell which has data. It is highly unreliable. Try this experiment." He then goes to show that formatting affects UsedRange.

http://www.rondebruin.nl/win/s9/win005.htm
Very respected Ron de Bruin says "Possible problems with xlCellTypeLastCell and UsedRange are:

The last cell will only re-set when you save (or save/close/reopen the file). If cell formatting is changed it will not reset the last cell, clearing the data is not enough, you must delete the rows or columns then,
See: http://www.contextures.com/xlfaqApp.html#Unused

So when using VBA you cannot rely on this macros if you want the last row or column with data on your worksheet."

Clearly, some high octane opposition to .UsedRange.

@Doc, just to clarify when I put Rows.Count Xlup I wasn't very clear I was actually referring to the use of it with .End i.e.

Please Login or Register  to view this content.

Apologies if I have typed it wrong as on my phone.

where the used range cannot be reset with a simple ActiveSheet.Usedrange command

@xlnitwit if you encounter it again then try...

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

as they normally do reset it.

BTW I would use "unexpected" rather than "unreliable".

Originally Posted by WasWodge

@xlnitwit if you encounter it again then try...

Please Login or Register  to view this content.

as that normally does reset it.

Believe me, we tried every conceivable version. Nothing worked short of the methods I indicated earlier.

I would reiterate however, that I do not believe there to be one answer to this question, not least because there will be different definitions of what is used dependent upon the end goal.

Strange, I have encountered the same issue with ActiveSheet.UsedRange but not with Application in front of it, but just because I haven't encountered it doesn't mean it doesn't happen (and I tend to avoid it anyway these days).

Originally Posted by xlnitwit

For information, using Activesheet.Usedrange is also not infallible in terms of resetting the actual used range. Though, again, it will suffice in the vast majority of cases.

- yeh for one probably when a range , rng, referred to by rng.SpecialCells is outside the UsedRange ???
_..............................

Anyway , Yep seem like referencing the Worksheet also refreshers / stops Excel remembering the UsedRange ( wish in my current work i could get Excel to forget its memory of the memory it used for a large file, after that file closes... ) .... So now here line 98 would also work n
'

Please Login or Register  to view this content.

So looks like it is not quite as dodgy to use as I thought the SpecialCells(xlCellTypeLastCell) . like everything once you know what you are doing..., but as I am not really too experienced or knowledged ( I am a beginner, I wish I was not . this stuff makes my brain hurt.. lol... ) , so I think based on what xlnitwit says, ( he is not a beginner ) I think I will still be wary about it. But I am wiser now thanks to this Thread.


_............................

Originally Posted by Oppressed1

.....As to your observation for an empty sheet: it is logical that Excel's normal "domain" is a set of addresses represented in a rectangle 256 by 65536 (or version 2007 & 2010, 65536 by 1048576; 2013 is another matter). Just speaking as a programmer, if they had to handle addresses outside those ranges it would be gruesome (for one thing, those specified limits were based on CPU and memory addressing limits for a byte and an word - a technical discussion, getting off track here). So setting a "floor" row of 1 and column of 1 is likely just a logical convention, given the aforementioned "domain" grid in versions going all the way back. (Hint: calculate 2^8 and 2^16.) (Maybe subtly related, also note that much of Excel is "1" based, not "0", though some items are under the user's control.)

Interesting, Thanks... gives some hint also why Arrays() in Worksheet Functions are still limited to the old Worksheets limit

Alan

P.s. seems was wrong about background color - it does indeed effect the UsedRange – I got that bit wrong , probably I confused a .Rows.Count with a .Row somewhere.
P.P.s
Of course If you want to find the last row with an entry than use the End(xlUp) way for a specific column and the xlPrevious for last row across the Worksheet, and if you want the last row including background color or most other formatting then this will probably.. mostly.. work ( ( but Oppressed said word to that effect as well "....

Please Login or Register  to view this content.

P.s.
@WosWedge
I knew / guessed what you meant



Great Thread, getting a bit out of my depth, but watching further with interest and larning from the real Experts

Originally Posted by WasWodge

.....
@xlnitwit if you encounter it again then try...

Please Login or Register  to view this content.

......

It is a common misunderstanding that In a normal macro module an unqualified range reference goes to the ActiveSheet. It does not it goes to the Application Range, which mostly then is the ActiveSheet.

This point is a bit off any points raised here, but I guess there might occasionally be situations when not handling that properly could throw a spanner in the works on some range referencing

Very naively, not knowing really what i am talking about, chucking in an extra Application in sounds like it could then change something ...

_...

http://www.excelforum.com/showthread...=1#post4483344

Originally Posted by Doc.AElstein

Forays Down the Excel Range Referencing
Addressing the Member Property .Address on referral Wonks
.........
At this Point it is worth pointing out a common mistake made. It is often said the Range ( ) reverts to the Active Worksheet Range objects in a Normal Code Module. This is in fact only true for the simplest case of
Line 183. = Range("A1")
Admittedly this is the most common usage, hence the confusion is understandable. When only a simple string like A1 is used, then VBA will at compile add the rest of the string reference to refer to the Active Worksheet. In
Lines 186, 190, 200 our Excel Application Range is being referred to the Cell referenced in the ( ) , which will be worksheets “BracketWonk” regardless of which Worksheet is Active. We will only get problems with the code lines from 186 if the code is run from a Worksheets Code module other than worksheets “BracketWonk”. If for example we ran from a Worksheet “Other”, then VBA would add at compile to the unqualified Range thus
Worksheets(“Other”).Range(
Hence we “go to” the Range Object Members of Worksheet “Other” and attempt a reference to a cell in another Worksheet. This will error !!

Line 210 takes the explicit Range referencing one step further. I would suggest I might be able to use a version of this code line to reference any Workbook anywhere, including closed workbooks if I could somehow free Range from applying to the open instance of Excel , which I expect it does by default. I expect that this is not possible.

I must go, I am really getting a head ache now..
_..lol..

Oh and of course

Please Login or Register  to view this content.

Oh back where we started.

Originally Posted by Oppressed1

....[code]activesheet.usedrange ' required. Picked this up from Walkenbach's utils 20 years ago
L = activesheet.cells.specialcells(xlCellTypeLastCell).row ' ......I have been unable to find a flaw in this
....

Hmm.... well you have almost converted me

activesheet.cells.specialcells(xlCellTypeLastCell).row ' ......I have been unable to find a flaw in this

http://www.rondebruin.nl/win/s9/win005.htm

and yes I know the link has already been posted once in the thread

Originally Posted by WasWodge

http://www.rondebruin.nl/win/s9/win005.htm...

Rondebruin says there ".....The last cell will only re-set when you save (or save/close/reopen the file)......" maybe something changed since he wrote that blog..... we know that we can also reset by either referencing .UsedRange in the code line, ( or using any of the properties of the .UsedRange ) In which case ( I think ? ) those codes lines will get the row of the last UsedRange. Maybe it is a question of what you think the code should do... his first code does this_...
.Range("A1")
_... one might think that means you should be restricted to the Range A1. Which sort of sounds better. But if you just accept that SpecialCells(xlCellTypeLastCell) will work on the UsedRange ( either the one in memory or the actual one if you refresh it as we have discussed ) then it returns the correct answer.

_ I think the original question was to find the last row across the Worksheet. If so those codes do work.

_ But clearly the things are not too well defined and open to interpretation.

EDIT: Post moved to here

http://www.excelforum.com/showthread...=1#post4487106

To prevent clutter in this Thread

A discussion of some weird and really weird characteristics of UsedRange and .SpecialCells( ___ ) and SpecialCells(xlCellTypeLastCell)
It may help you to decide why not just use SpecialCells? - It sure do make me nervous!!!

Doc you're certainly ferocious about tearing into a problem! Let me mention a couple of things - you probably can omit the command "Let" in every case, and you can always omit line numbers (although they are really useful for referring to in online discussions like this) in favor of text line labels, and those are only required once in a while (that is, when code refers to them). You have a very busy screen on your last post, and there's so much there, that thinning it out might enhance comprehension of your code. It would also speed up comprehension of your code, not just by others, but probably yourself. As to using line numbers for online discussion (that's a great idea), you can just do that on lines that you specifically refer to, reducing some clutter. This is all generally speaking; do what works for you.

Another tip, maybe you already have, but if not, google "option explicit" whose use is heavily recommended except for those who are both fully aware of potential consequences, and who are careful enough to avert them. It's somewhat like not putting on a seatbelt to go down your driveway to the mailbox.

Now to your code - 110 is mysterious. This belongs in another thread so I'll only say quickly (then move to another thread if you want) that sometimes code references like "B3" are not referring specifically to cell B3, but rather indicate an offset from the range otherwise implied by using the reference "A1" - i.e., it's like issuing an .offset(2,1). That MIGHT be the case here - I haven't dug into it.

Again all this goes in another thread. If you to pursue further, let's take it there, rather than muddy this already long thread.

Originally Posted by WasWodge

activesheet.cells.specialcells(xlCellTypeLastCell).row ' ......I have been unable to find a flaw in this

{}http://www.rondebruin.nl/win/s9/win005.htm

and yes I know the link has already been posted once in the thread

Ron is a master, but read what he wrote - he only indicated a "problem" with its sensitivity to save/close/reopen, and to formatting; but I believe that his implication is, a "problem to those who aren't careful or who don't understand these conditions." He continues, "you cannot rely on this" (UsedRange), but once again, I believe his implication is "you cannot rely on this unless you understand that it gives a different result than what you would receive after issuing "sht.usedrange" or received after saving or reopening the sheet, or if you don't understand that all cells with nonconditional formatting are counted in UsedRange, even if such cells' contents are cleared."

That's a real real lot of words. It's easier just to say "you cannot rely" than to say all that. It's my growing belief that this is exactly what was conveyed by him and a couple of other MVPs, and armchair bloggers started the general contagion in belief that it's "unreliable." Methinks most who parrot that word (not you guys reading this, or the MVPs ) don't even understand what "unreliable" actually conveyed when originally stated - that it's not failure-of-process "unreliable," but rather, that it gives different results based on conditions, which, if understood, give consistent and reliable operation. (Grooooan - unless a corrupt workbook.)

The upshot of all this, in my humble opinion:

Short answer, very specifically, not meaning to act didactic: issue "activesheet.UsedRange" (or sht.UsedRange) as a solo command prior to using UsedRange in code or xlCellTypeLastCell, and you will get exactly what Excel regards as the last used cell. If you only want "last used cell with contents" then do something else.

That is, unless I'm mistaken

I agree it is understanding the idiosyncrasies of any methods which is the important part (by the way I can read what he wrote), but personally I prefer to use methods where I don't have to force an update of the used range just to get the result I desire.
But that is my personal preference.

To state "I have been unable to find a flaw in this" is blatantly incorrect if you have to use another method first to reset it.

BTW, as previously stated in the thread "activesheet.UsedRange" and "sht.UsedRange" as written do not always reset the used range.


I will leave it there now as we both agree that the important thing is to understand what the issues are rather than that they have issues.

Originally Posted by WasWodge

BTW, as previously stated in the thread "activesheet.UsedRange" and "sht.UsedRange" as written do not always reset the used range.

You say that rather matter of factly but there's nothing but anecdotal statements to support it. I've never seen it happen, and to my knowledge no one has ever produced a workbook that does that. The only thing even close - but not the same at all - is when a column or row is undeletable on a corrupt workbook; but then, even then, LastCell and xlCellTypeLastCell still do as they should - they show exactly what Excel shows as the last cell.

I respect that the anecdotal recollections count for something, and likely suggest something odd is in play, but don't warrant the assertion you make above. IMHO.

Moreover, if you can show any method that shows the end of a spreadsheet (what Control-End shows) and show that LastCell and xlCellTypeLastCell do not, then we've got something. You do realize that the only alternatives mentioned, the only ones, are to find the last cell "with data" ? Those are fine suggestions, if you only want to find the last cell with contents.

To state "I have been unable to find a flaw in this" is blatantly incorrect if you have to use another method first to reset it

Ooh, the B word, eh. Let's be clear here: LastCell and xlCellTypeLastCell show exactly what Excel shows as the last cell - whether sht.UsedRange is issued or not. I think a great number of people are unclear on that. (And really, now, would you not use API functions because you have to do something else (declare them) first? Would you refuse to use 'find again' because you have to use 'find' first?)

Originally Posted by Oppressed1

but then, even then, LastCell and xlCellTypeLastCell still do as they should - they show exactly what Excel shows as the last cell.

I believe there is a miscommunication here. I do not think that anyone- certainly not I- was suggesting that they would differ from each other.

The issue is that both UsedRange and xlCelltypelastcell can be wrong. By which I mean that they refer to ranges that no longer have any data or formatting applied to them and can therefore not, in any real sense, be described as used. Whether that constitutes a problem will depend entirely on circumstance. If one were copying a formula to the last row or columns, one would not generally want it to populate beyond the real used range. However, if one is merely processing data it ought not to be a serious issue other than in terms of doing more work than strictly necessary.

Originally Posted by Oppressed1

You say that rather matter of factly but there's nothing but anecdotal statements to support it. I've never seen it happen, and to my knowledge no one has ever produced a workbook that does that,

I don't need to provide examples as there have been quite enough examples posted over the years that you can Google.

Ooh, the B word, eh. Let's be clear here: LastCell and xlCellTypeLastCell be clear here: LastCell and xlCellTypeLastCell show exactly what Excel shows as the last cell - whether sht.UsedRange is issued or not.

Obviously you are happy that xlCellTypeLastCell doesn't always refer to the last cell with either contents or formatting unless you reset the UsedRange and so who am I to differ (I didn't ask the question only gave an my feelings on the matter) so each to their own...

As for Find I treat it as it was intended as a single find exactly as it is manually and Microsoft documented (and not stated as an issue).

I wish you well.

Hi Oppressed

Originally Posted by Oppressed1

... "activesheet.UsedRange" (or sht.UsedRange) as a solo command prior to using UsedRange in code or xlCellTypeLastCell, and you will get exactly what Excel regards as the last used cell. ...:

As I mentioned in Post #18 , I am almost converted, to use somethiing like
= Worksheet.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row ( or .column )
'2d For last row ( or column ) in any column ( or row ) with entries or most formats.
I am just a bit nervous of using something that has clearly some weird characteristics._......

_.....As for the rest. Thanks for the Feedback. I recieve any feedback gratefully .
I think I was adding to the discussion in a relevant way. Clearly this thing, as you yourself suggest, cannot , or should not be explained in a few black and white text..like ..."you cannot rely….“

_My first Post re emphasised the memory issue.

_My last showed up some very weird and interesting characteristics IMO of the UsedRange – SpecialCells - (xlCellTypeLastCell). Very relevant IMO in a discussion thereof, and to the question “why not use it” - the Thread Title, which will atract others on search here asking that question.. I am sure many like me would be wary of using something with such characteristics. _....

_.... But I have dropped the bigger Posts off in an appendix Thread and put a link in the Original posts to prevent cluttering your Thread.

_... There I Let myself use code lines sometimes, and always use Option Explicit. ( I have not a lot of experience, but to date only experienced positive effects from using option explicit. As It appears at the top of a Code Module, I may not always copy it across when I post Code snippets from somewhere down in a Module)

I will not start another Thread concerning those realy wierd effects. – I do not have an actual problem with it, and IMO it is very relevant to this Thread.
We do not know at this stage whether that might give some indication of when the .UsedRange.Cells.SpecialCells(xlCellTypeLastCell) might break.

_.....................

So I am converted to use it occasionally.. I will certainly post back here if it ever breaks !!

_.........................................

My last short b/w summary as I see it... ( Please be gentle to me I am a Layman )_.......

To get SpecialCells(xlCellTypeLastCell) to find, for example, the last row in any column across the sheet... _....all my experiments suggest this would do it in a worksheet code module
=UsedRange.SpecialCells(xlCellTypeLastCell).Row

It appear that using the UsedRange such, is one way that will reset the memory, which is the key to it working. ( And as you say .. using....UsedRange.. as a solo command prior to using SpecialCells(xlCellTypeLastCell) would also suffice. Opening and closing or using any of the Properties of UsedRange would also appear to reset this memory )

For a normal Code module that last line will error, ( Possibly as the Application has no UsedRange ? ) so you need
= Worksheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

Most SpecialCells things are confined at the top end to the UsedRange top end, or the top end of some Range Object , rng, you reference it to by
_____rng. SpecialCells(____)
That is not the case with xlCellTypeLastCell strangely. It always appears to apply to the UsedRange, ( be it the actual or memory thereof )
As there are other strange goings on which I discussed, maybe it does no harm to use Cells , but it does not appear necessary.

It is the use of a “call”” to UsedRange which is the important thing, either in a previous code line, in which case_...
Worksheet.rng.SpecialCells(xlCellTypeLastCell).Row
_....would work. But it is probably neater to do it in the line
= Worksheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
Or if you prefer.. this appares to do no harm....

= Worksheet.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row

Best wishes
Alan






























Edit P.s.
Just stumbled over a few links
http://dmcritchie.mvps.org/excel/lastcell.htm
http://www.mrexcel.com/forum/excel-q...s-ignored.html
https://fastexcel.wordpress.com/2011...ed-range-woes/

P.P.s.
Could Muddy up the Thread a bit with a discussiion on the CurrentRegion Prooperty alternative, if you like...
http://www.excelforum.com/excel-gene...ml#post4479069