Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Finding first available row - which is better?

Posted on 2011-10-17
18
Medium Priority
?
170 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:dlmille
  • 7
  • 5
  • 3
  • +2
18 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 1200 total points
ID: 36981553
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
 
LVL 42

Author Comment

by:dlmille
ID: 36981621
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 36981767
I think the first is more efficient as it is looking at only one column.

The keyword is "I think"
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Expert Comment

by:gowflow
ID: 36981877
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 36981906
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
 
LVL 42

Author Comment

by:dlmille
ID: 36981945
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
 
LVL 42

Author Comment

by:dlmille
ID: 36982013
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
 
LVL 31

Expert Comment

by:gowflow
ID: 36982041
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 800 total points
ID: 36984805
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
 
LVL 42

Author Comment

by:dlmille
ID: 36986816
>>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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36986945
Yes - if you don't specify them, it uses whatever was last used.
0
 
LVL 42

Author Comment

by:dlmille
ID: 36987711
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
 
LVL 42

Author Comment

by:dlmille
ID: 36987726
talk about a pain.

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

Expert Comment

by:redmondb
ID: 37000398
goflow,

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

Regards,
Brian.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37000479
>>"help is abysmal etc"

That's not really specific to this question though. :)
(Imagine what it will be like by by Office 2020!)
0
 
LVL 42

Author Comment

by:dlmille
ID: 37003506
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37003537
The Help example for the Find function is actually wrong anyway, so it's worse than just unhelpful.
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 800 total points
ID: 37005918
A useful reference article (and download) by Jim Cone: http://blog.contextures.com/archives/2011/07/18/find-last-row-with-excel-vba/
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question