Finding first available row - which is better?

Which would be a best practice?  Or does it just depend on one's knowledge of the data in the sheet?

dim rng as Range

set rng = Range("A" & Rows.Count).end(xlUp).Offset(1,0)

or

set rng = Range("A" & cells.Find(what:="*",SearchDirection:=xlPrevious).Offset(1,0).Row)

Certainly, with the second, (assuming there was any data in the ActiveSheet) you'd be assured you really got the first entirely blank row.  Is there any reason one may not want to just revert to the .Find approach as superior in all cases?
LVL 42
dlmilleAsked:
Who is Participating?
 
Saqib Husain, SyedEngineerCommented:
The first one is restricted to a particular column. If all the work is to be performed with respect to a particular column then the first one is better.

If you are looking for a new row regardless of the column then the second one is better.
0
 
dlmilleAuthor Commented:
Thanks, ssaqibh - I was going to post this in your solution, but thought breaking this out into a question for the experts would be a better place to debate, if any.

My thoughts were the same - if you know what your data looks like and you know column A will always have data, the first is certainly easier to debug - but, is it more efficient?  or is Excel processing indifferent.

I also wanted to get other's thoughts on this, as I've seen some of the experts use the latter, .Find, approach more often than not, while I've almost use the former, .End(xlUp) approach.

Dave
0
 
Saqib Husain, SyedEngineerCommented:
I think the first is more efficient as it is looking at only one column.

The keyword is "I think"
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
gowflowCommented:
I personally use
usedrange and it has worked brillantly for all cases but have seen comments (and would like to know) saying that usedrange is somehow not reliable and does not return correct values ... Any idea why ?
gowlfow
0
 
Saqib Husain, SyedEngineerCommented:
When you delete rows or columns from data the used range does not retract accordingly. It remembers the last setting of the used range.
0
 
dlmilleAuthor Commented:
Also, formatting is included in usedRange, goflow, so you'd get errant values just because some cell had a color fill, or other formatting change.
0
 
dlmilleAuthor Commented:
goflow - usedRange will work for a while, then sometimes its results are unpredictable.

Here's a link on an approach to obtaining a "real" used range set of parameters (note this was an Excel 2003 solution, so slight modification to bring it up to speed - like using rows.count and columns.count, rather than hard-coding max range numbers):http://www.vbaexpress.com/kb/getarticle.php?kb_id=82

Dave
0
 
gowflowCommented:
yes correct (only if the format has changed if only data was input without format change then it picks up correctly) you mean when u simply use the delete button and empty cells not using the entirerow delete and when cells hve seen their format change from the default, then yes usedrange will still rmember the last row that contained data and will give wrong value.
gowflow
0
 
Rory ArchibaldCommented:
It does depend on what you want. There might for example be totals in the last rows in columns to the right, and you want to find the last row of data before those totals, so you check column A only (though again, you could use Find for that too)
However, if you are going to use Find you really need to specify all the arguments, especially a start cell!
Also, neither is particularly reliable if your data is filtered...
0
 
dlmilleAuthor Commented:
>>Also, neither is particularly reliable if your data is filtered...

I had the assumption (and thought I saw documentation on that) that FIND works "fine" with xlFormulas, but has problems with filtered data using xlValues.

What unintended consequences if some parameters are left off?  Could it be it uses whatever is currently the settings in the last FIND command?  That WOULD be an unintended consequence...
0
 
Rory ArchibaldCommented:
Yes - if you don't specify them, it uses whatever was last used.
0
 
dlmilleAuthor Commented:
So, I guess one would have to reset the find parameters with sub (baggage...) or set all parameters (the latter probably best - time to change my habits), and if there's a chance a filter is active, set autoFilterMode = False, or do the find with xlFormulas (knowing that would work based on the data).

While the second approach has its uses, all the sudden, I'm liking the first approach, unless again, for some reason, I just don't know which column has the last row...

Dave
0
 
dlmilleAuthor Commented:
talk about a pain.

The .Find method doens't give you autosense popups for each of the parameters, help is abysmmal, etc.
0
 
redmondbCommented:
goflow,

The biggest gotcha for me with UsedRange is that it excludes blank initial rows.

Regards,
Brian.
0
 
Rory ArchibaldCommented:
>>"help is abysmal etc"

That's not really specific to this question though. :)
(Imagine what it will be like by by Office 2020!)
0
 
dlmilleAuthor Commented:
I get you, but it is relevant, if we're discussing, "which is better?".

Help for range selection in the first option is more thorough (perhaps because its simpler) than with the .Find command.

Efficiency for the developer is as relevant as the function.

Cheers,

Dave
0
 
Rory ArchibaldCommented:
The Help example for the Find function is actually wrong anyway, so it's worse than just unhelpful.
0
 
Rory ArchibaldCommented:
A useful reference article (and download) by Jim Cone: http://blog.contextures.com/archives/2011/07/18/find-last-row-with-excel-vba/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.