What do these lines mean?

[AB33]

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.

[Thalassa]

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

[JBeaucaire]

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.

[AB33]

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?

[Richard Buttrey]

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 !

[AB33]

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

[JBeaucaire]

@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.

[Richard Buttrey]

@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.

[JBeaucaire]

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.

[AB33]

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.

[Thalassa]

They are essentially identical.

[JBeaucaire]

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.

[JoeMo]

@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.

[JBeaucaire]

@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.

[AB33]

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.

[WasWodge]

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.

[AB33]

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

[AB33]

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.

[WasWodge]

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).

[JosephP]

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.

[JBeaucaire]

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.

[AB33]

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