+ Reply to Thread
Results 1 to 22 of 22

What do these lines mean?

  1. #1
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    What do these lines mean?

    I have seen(The below code) the first line quite a lot on this forum, and the second one the book I am reading at the moment.

    I have a vague idea of the first line. It is finding the last row on ws2, but I am lost by the star sign "*"? What is LR finding? What is a star?

    I have also vague idea on the second one. Cell address return an absolute cell reference, but ,lost on the meaning of "!"
    Please help me to explian in plain English.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  2. #2
    Forum Contributor
    Join Date
    06-07-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    189

    Re: What do these lines mean?

    LR is finding the last row with a value, looking up from the bottom of the sheet.

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: What do these lines mean?

    The asterisk is a wildcard. It is saying Find "anything" in the last cell used on the worksheet, then log the row into the LR variable.


    The ! is a normal part of a cell referencing one sheet to another. See this for yourself by going to any sheet... select a cell, press the = sign, then switch to another sheet, click any cell, and press ENTER.

    Now look at the resulting sheet reference formula you created. The ! is just part of the syntax indicating the end of the sheet name.

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: What do these lines mean?

    Jerry,
    Thanks!

    "Find "anything" in the last cell used on the worksheet". Could it be anythig on the cell, be it formula, text,number or any value?

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: What do these lines mean?

    Hi,

    In this example the * is being used as a wildcard character meaning find anything. Since the search direction is set to 'xlPrevious' and the search has been set to start with the last sheet row and column, (i.e. 1048576 & XFD). The find statement will therefore find the first non blank cell moving up and to the left and return the row number to the variable LR.

    A simpler instruction would have been
    LR = ws.UsedRange.Rows.Count

    The ! is just the character used to indicate a sheet name. If you look in any cell which has a formula that references a cell on another sheet you'll see that that sheet name finishes with a !

  6. #6
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: What do these lines mean?

    Richard! Brillant! I understand not only "Used range",but it is also easy to remember as a line, particularly as someone who is new to VBAs

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: What do these lines mean?

    @Richard,
    Please Login or Register  to view this content.

    I've stopped using that method as Excel sometimes "lies" about the answer. On occasion it remembers the UsedRange at it's greatest value even if you have recently cleared a bunch of data at the bottom resulting in less rows. The "*" method is searching for an actual displayed value, so that seems to get the actual last row.

    Now, when Excel gives the wrong answer, it is always a higher number of rows, not a lesser, so this may be harmless, but I find it annoying, so avoid the method altogether.


    EDIT: By "lies" I mean a "count of rows" is not the same thing as the "last row with data". If you open a new sheet, and enter a new value in cell B6 only, the UsedRange.Rows.Count method will say =1, which is wrong. you would expect the answer to be =6.
    Last edited by JBeaucaire; 06-08-2012 at 03:40 PM.

  8. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: What do these lines mean?

    @JB

    Good point, and one I overlooked.

    As you say the used range can give the wrong results when data has been deleted from cells resulting in a new blank row unless the workbook is first saved - which seems to have the effect of resetting the used range.

  9. #9
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: What do these lines mean?

    For clarity on this point and thread:

    1) I prefer to control myself which "column" is used to determine how far my data goes down the sheet. With some planning, I can skip past "down below" summaries and such by keeping this column free of anything but raw data. If it were column A:

    Please Login or Register  to view this content.

    2) The second method is the one demonstrated above, using the entire worksheet, we search for absolute last cell in any column with something in it:
    Please Login or Register  to view this content.



    @AB33 - If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.

  10. #10
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: What do these lines mean?

    My undetstanding is both these below lines are the same. Am I right? Which one is more agile and flexible in programming?

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    06-07-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    189

    Re: What do these lines mean?

    They are essentially identical.

  12. #12
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: What do these lines mean?

    Neither is more agile. If you KNOW the column you're going to be using, the RANGE() method is easier to read and would work exactly as efficiently.

    If you need the Column reference to be numeric (possibly feed that in from another function), then the CELLS() method allows you to use a numeric entry for the column and would be used in that case.


    Interestingly, you could use this, too, and it would also work:
    Please Login or Register  to view this content.
    That might lead you to conclude the CELLS() is more flexible, but that's up to you to decide.
    Last edited by JBeaucaire; 06-10-2012 at 06:53 PM.

  13. #13
    Registered User
    Join Date
    05-16-2012
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: What do these lines mean?

    Quote Originally Posted by JBeaucaire View Post
    @Richard,
    EDIT: By "lies" I mean a "count of rows" is not the same thing as the "last row with data". If you open a new sheet, and enter a new value in cell B6 only, the UsedRange.Rows.Count method will say =1, which is wrong. you would expect the answer to be =6.
    Agree with your caution about using UsedRange to find the real last row. It is not reliable. But, I disagree with your edit.
    If you open a new sheet and make a single entry in B6, then the used range is cell B6 and the count of rows in the used range is =1 NOT 6. On any new sheet, select a range like B6:D20 and enter data. Now select A1 but do not make an entry. Now using the VBE immediate window enter Activesheet.usedrange.select and note that none of the rows above row 6 are selected.

  14. #14
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: What do these lines mean?

    @JoeMo:

    Remember to keep the thread in context with the discussion. My statement is correct in that this thread is about reliable methods for determing the "last row with data in it". For this, my example is correct in that usage of the UsedRange method gives inaccurate results. And it does so because of your point.

  15. #15
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: What do these lines mean?

    I think I wiil close this thread, but have a last question. I now understand the difference between "*" and used range. To a learner, it would have been easier to use the used.range, but it has its own limitation.

    I have also seen a very long searching line of code from Ron's site. Please see below.

    Please Login or Register  to view this content.

    I am wondering if this line of code L
    Please Login or Register  to view this content.
    is the same as Ron's one. Ron's one is a very long line of code, and would not be eassier to learn it.

  16. #16
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: What do these lines mean?

    Below is a quote (if I remember right it was by Rick Rothstein Microsoft MVP, but I might be mistaken). It explains probably partly why Ron has done it like that. The top 2 lines and code were by someone else responding to a question.

    If you're looking to do it with vba you can use something like the following:

    Please Login or Register  to view this content.
    This will also find the last used row no matter what column it's found in.

    I just want to point out a couple of things about the above code line. First, since you are searching using xlPrevious, and since you want the "last" row, you can leave out the After argument as the search starts from the top-left cell of the specified range by default (A1 in this case because all cells are specified). So this will do the same as what you posted...
    Code:
    Please Login or Register  to view this content.
    However, a point needs to be made about your missing LookIn argument. Because the Find method "remembers" the setting for the last time Find was used on the system, you have no way of knowing if it is set to xlValues or xlFormulas and each of these could produce different results depending on what is on the worksheet. xlValues will find the last displayed piece of data no matter if that data is a constant value or the non-empty result of a formula. On the other hand, xlFormulas will find the last filled-in cell even if that cell is a formula displaying the empty string (""). So, to summarize, using LookIn:=xlValues will ignore formulas displaying the empty string meaning if you use LstRw to establish a row after which to begin copying data/formulas to, you could end up overwriting existing formulas (if they are displaying the empty string) whereas using LookIn:=xlFormulas will locate the actual last used row even if it "looks" like nothing is there (because of a formula displaying the empty string). So, depending on what is needed, I think PatPat should use one of these...

    ' Find last row displaying visible data
    Please Login or Register  to view this content.
    ' Find last row with anything in it (including formulas displaying the empty string ("")
    Please Login or Register  to view this content.
    Last edited by WasWodge; 06-10-2012 at 08:12 AM.
    If my solution worked (or not) please let me know. If your question is answered then please remember to mark it solved

    Computers are like air conditioners. They work fine until you start opening windows. ~Author Unknown

  17. #17
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: What do these lines mean?

    WasWodge! Thanks for your input and shall wait for others to add thier views

  18. #18
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: What do these lines mean?

    Please Login or Register  to view this content.
    Am I right that If I add the LookIn:=xlFormulas to the second, both are the same, but the second also searches for columns? Or do both search for columns as well.

  19. #19
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: What do these lines mean?

    The first code will search columns and rows. The second wil give an unqualified reference. The easy way to test is to Create a block of data, change the last .Row to .select, select cell A1 and run the code. What cell does it select?
    The LookIn:=xlFormulas just forces it to look at cells that contain values or formulas (including empty string results).

  20. #20
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: What do these lines mean?

    the second one is technically wrong since it tells you to search backwards from the last cell in the sheet, but that could actually be the last used cell and the only used cell on that row.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  21. #21
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: What do these lines mean?

    Yes, it's the same. I think you can learn from it, it is laid out in a way that each parameter in the FIND() is on a line of its own with the parameter name, making it clear what it restricts. Once you understand all the options available in the FIND, then the shorter version is just as preferable as any other.


    For me, the critical parameters to always include would be LookIn:= and LookAt:= since those settings persist in the workbook, keep control of them.



    If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.

  22. #22
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: What do these lines mean?

    Thanks all for your time and expertise. Yes, I know now there is a short version of find. The thread is now closed

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1