2012-06-23 12:20:51 8 Comments

When I want to find the last used cell value, I use:

```
Dim LastRow As Long
LastRow = Range("E4:E48").End(xlDown).Row
Debug.Print LastRow
```

I am getting the wrong output when I put a single element into a cell. But when I put more than one value into the cell, the output is correct. What's the reason behind this?

### Related Questions

#### Sponsored Content

#### 42 Answered Questions

#### 4 Answered Questions

### [SOLVED] Find the last not empty row in a range of cells holding a formula

**2015-11-28 12:18:38****Pascale****3646**View**9**Score**4**Answer- Tags: excel vba excel-vba excel-2013

#### 22 Answered Questions

#### 13 Answered Questions

#### 1 Answered Questions

### [SOLVED] Excel VBA code for simple formula between cells

**2017-07-26 10:07:27****Rafael Osipov****351**View**0**Score**1**Answer- Tags: excel vba excel-formula range

#### 1 Answered Questions

#### 2 Answered Questions

#### 3 Answered Questions

### [SOLVED] Insert formula with Variable VBA

**2017-03-03 05:02:50****Swagayema****2054**View**0**Score**3**Answer- Tags: excel vba excel-vba excel-formula

## 12 comments

## @Nickolay 2018-04-22 23:01:27

Since the original question is about

problemswith finding the last cell,in this answer I will list the various ways you can get unexpected results; see my answer to"How can I find last row that contains data in the Excel sheet with a macro?"for my take on solving this.I'll start by expanding on the answer by sancho.s and the comment by GlennFromIowa, adding even more detail:

Other things you might want to consider:

hidden rows(e.g. autofilter),blank cellsor blank rows?With that in mind, let's see

how the common ways of getting the "last cell" can produce unexpected results:`.End(xlDown)`

code from the question will break most easily (e.g. witha single non-empty cellor when there areblank cells in between) for the reasons explained in the answer by Siddharth Rout here (search for"xlDown is equally unreliable.") 👎`Count`

ing (`CountA`

or`Cells*.Count`

) or`.CurrentRegion`

will also break in presence of blank cells or rows 👎`.End(xlUp)`

to search backwards from the end of a column will, just as CTRL+UP, look fordata(formulas producing a blank value are considered "data") invisible rows(so using it with autofilter enabled might produce incorrect results ⚠️).You have to take care to avoid the standard pitfalls (for details I'll again refer to the answer by Siddharth Rout here, look for the

"Find Last Row in a Column"section), such as hard-coding the last row (`Range("A65536").End(xlUp)`

) instead of relying on`sht.Rows.Count`

.`.SpecialCells(xlLastCell)`

is equivalent to CTRL+END, returning the bottom-most and right-most cell of the "used range", so all caveats that apply to relying on the "used range", apply to this method as well. In addition, the "used range" is only reset when saving the workbook and when accessing`worksheet.UsedRange`

, so`xlLastCell`

might produce stale results⚠️ with unsaved modifications (e.g. after some rows were deleted). See the nearby answer by dotNET.`sht.UsedRange`

(described in detail in the answer by sancho.s here) considersboth data and formatting(though not conditional formatting) andresets the "used range" of the worksheet, which may or may not be what you want.Note that a common mistake ️is to use

`.UsedRange.Rows.Count`

⚠️, which returns thenumber of rowsin the used range, not thelast row number(they will be different if the first few rows are blank), for details see newguy's answer toHow can I find last row that contains data in the Excel sheet with a macro?`.Find`

allows you to find the last row with any data (including formulas) or a non-blank valuein any column. You can choose whether you're interested in formulas or values, but the catch is that itresets the defaults in the Excel's Find dialog️️⚠️, which can be highly confusing to your users. It also needs to be used carefully, see the answer by Siddharth Rout here (section"Find Last Row in a Sheet")`Cells`

' in a loop are generally slower than re-using an Excel function (although can still be performant), but let you specify exactly what you want to find. See my solution based on`UsedRange`

and VBA arrays to find the last cell with data in the given column -- it handles hidden rows, filters, blanks, does not modify the Find defaults and is quite performant.Whatever solution you pick, be careful

`Long`

instead of`Integer`

to store the row numbers (to avoid getting`Overflow`

with more than 65k rows) and`Dim ws As Worksheet ... ws.Range(...)`

instead of`Range(...)`

)`.Value`

(which is a`Variant`

) avoid implicit casts like`.Value <> ""`

as they will fail if the cell contains an error value.## @Vityata 2018-06-01 19:30:44

For the last 3+ years these are the functions that I am using for finding last row and last column per defined column(for row) and row(for column):

## Last Column:

## Last Row:

For the case of the OP, this is the way to get the last row in column

`E`

:`Debug.Print lastRow(columnToCheck:=Range("E4:E48").Column)`

## @sancho.s 2014-12-24 13:34:51

As to the correct way of finding the last used cell, one has first to decide what is considered. I conceive at least three meanings:used, and then select a suitable methodUsed = non-blank, i.e., having

data.Used = "... in use, meaning the section that contains

data or formatting." As per official documentation, this is the criterion used by Excel at the time of saving. See also this. If one is not aware of this, the criterion may produce unexpected results, but it may also be intentionally exploited (less often, surely), e.g., to highlight or print specific regions, which may eventually have no data. And, of course, it is desirable as a criterion for the range to use when saving a workbook, lest losing part of one's work.Used = "... in use, meaning the section that contains

data or formatting"or conditional formatting.Same as 2., but also including cells that are the target for any Conditional Formatting rule.How to find the last used cell depends on what.youwant (your criterion)For criterion 1, I suggest reading this answer. Note that`UsedRange`

is cited as unreliable. I think that is misleading (i.e., "unfair" to`UsedRange`

), as`UsedRange`

is simply not meant to report the last cell containing data. So it should not be used in this case, as indicated in that answer. See also this comment.For criterion 2,, as compared to other options also designed for this use. It even makes it unnecessary to save a workbook to make sure that the last cell is updated.`UsedRange`

is the most reliable optionCtrl+Endwill go to a wrong cell prior to saving (“The last cell is not reset until you save the worksheet”, from http://msdn.microsoft.com/en-us/library/aa139976%28v=office.10%29.aspx. It is an old reference, but in this respect valid).For criterion 3, I do not know any built-in method. Criterion 2 does not account for Conditional Formatting. One may have formatted cells, based on formulas, which are not detected by`UsedRange`

orCtrl+End. In the figure, the last cell is B3, since formatting was applied explicitly to it. Cells B6:D7 have a format derived from a Conditional Formatting rule, and this is not detected even by`UsedRange`

. Accounting for this would require some VBA programming.As to your specific question:What's the reason behind this?Your code uses the first cell in your range E4:E48 as a trampoline, for

jumpingdown with`End(xlDown)`

.The "erroneous" output will obtain if there are no

non-blankcells in your range other than perhaps the first. Then, you areleaping in the dark, i.e., down the worksheet (you should note the difference betweenblankandempty string!).Note that:

If your range contains non-contiguous non-blank cells, then it will also give a wrong result.

If there is only one non-blank cell, but it is not the first one, your code will still give you the correct result.

## @GlennFromIowa 2017-04-12 18:42:05

I agree that

one has first to decide what is considered used. I see at least 6 meanings. Cell has: 1) data, i.e., a formula, possibly resulting in a blank value; 2) a value, i.e., a non-blank formula or constant; 3) formatting; 4) conditional formatting; 5) a shape (including Comment) overlapping the cell; 6) involvement in a Table (List Object). Which combination do you want to test for? Some (such as Tables) may be more difficult to test for, and some may be rare (such as a shape outside of data range), but others may vary based on the situation (e.g., formulas with blank values).## @M-M 2017-05-08 21:48:48

However this question is seeking to find the last row using VBA, I think it would be good to include an array formula for worksheet function as this gets visited frequently:

You need to enter the formula without brackets and then hit

Shift+Ctrl+Enterto make it an array formula.This will give you address of last used cell in the column D.

## @J. Chomel 2017-05-17 15:23:25

I was looking for a way to mimic the

CTRL+Shift+End, so dotNET solution is great, except with my Excel 2010 I need to add a`set`

if I want to avoid an error:and how to check this for yourself:

## @Siddharth Rout 2012-06-23 13:33:16

NOTE: I intend to make this a "one stop post" where you can use the`Correct`

way to find the last row. This will also cover the best practices to follow when finding the last row. And hence I will keep on updating it whenever I come across a new scenario/information.Unreliable ways of finding the last rowSome of the most common ways of finding last row which are highly unreliable and hence should never be used.

`UsedRange`

shouldNEVERbe used to find the last cell which has data. It is highly unreliable. Try this experiment.Type something in cell

`A5`

. Now when you calculate the last row with any of the methods given below, it will give you 5. Now color the cell`A10`

red. If you now use the any of the below code, you will still get 5. If you use`Usedrange.Rows.Count`

what do you get? It won't be 5.Here is a scenario to show how

`UsedRange`

works.`xlDown`

is equally unreliable.Consider this code

What would happen if there was only one cell (

`A1`

) which had data? You will end up reaching the last row in the worksheet! It's like selecting cell`A1`

and then pressingEndkey and then pressingDown Arrowkey. This will also give you unreliable results if there are blank cells in a range.`CountA`

is also unreliable because it will give you incorrect result if there are blank cells in between.And hence one should avoid the use of

`UsedRange`

,`xlDown`

and`CountA`

to find the last cell.## Find Last Row in a Column

To find the last Row in Col E use this

If you notice that we have a

`.`

before`Rows.Count`

. We often chose to ignore that. See THIS question on the possible error that you may get. I always advise using`.`

before`Rows.Count`

and`Columns.Count`

. That question is a classic scenario where the code will fail because the`Rows.Count`

returns`65536`

for Excel 2003 and earlier and`1048576`

for Excel 2007 and later. Similarly`Columns.Count`

returns`256`

and`16384`

, respectively.The above fact that Excel 2007+ has

`1048576`

rows also emphasizes on the fact that we should always declare the variable which will hold the row value as`Long`

instead of`Integer`

else you will get an`Overflow`

error.## Find Last Row in a Sheet

To find the

`Effective`

last row in the sheet, use this. Notice the use of`Application.WorksheetFunction.CountA(.Cells)`

. This is required because if there are no cells with data in the worksheet then`.Find`

will give you`Run Time Error 91: Object Variable or With block variable not set`

## Find Last Row in a Table (ListObject)

The same principles apply, for example to get the last row in the third column of a table:

## @Jean-François Corbett 2012-06-23 20:46:20

+1 but what's an

Effectivelast row?## @Siddharth Rout 2012-06-23 20:51:05

@Jean-FrançoisCorbett: Probably wrong choice of word by me? I mean the actual Last Row. The

`Actual`

last row may not be the same as the`Last Row`

that we might get using`UsedRange`

## @phan 2012-08-13 19:32:44

Siddharth Rout can you please elaborate and explain your last comment? Why might you get two different values for the 2 methods you describe?

## @Siddharth Rout 2012-08-13 19:48:04

@phan: Type something in cell A5. Now when you calculate the last row with any of the methods given above, it will give you 5. Now color the cell A10 red. If you now use the any of the above code, you will still get 5. If you use

`Usedrange.Rows.Count`

what do you get? It won't be 5. Usedrange is highly unreliable to find the last row.## @Carl Colijn 2014-01-31 12:34:08

Do note that .Find unfortunately messes up the user's settings in the Find dialog - i.e. Excel only has 1 set of settings for the dialog, and you using .Find replaces them. Another trick is to still use UsedRange, but use it as an absolute (but unreliable) maximum from which you determine the correct maximum.

## @Siddharth Rout 2014-01-31 12:37:10

@CarlColijn: I wouldn't call it messing. :) Excel simply

`remembers`

the last setting. Even when you manually do a`Find`

, it remembers the last setting which in fact is a boon if one knows this "fact"## @Keith Park 2014-08-29 07:09:56

Thank you for your great answer!. It helped me a lot. I'd like to translate this article to share with my Korean friends. It will be posted here ctrlaltdel Please let me know if you mind it. then I'll delete it.

## @Siddharth Rout 2014-08-29 07:11:44

@KeithPark: Please go ahead :) Knowledge only has a meaning if it is spread :)

## @Siddharth Rout 2014-08-29 07:13:31

@KeithPark: I wouldn't mind if you pointed a link back to this site as well :P

## @Keith Park 2014-08-29 08:50:43

@SiddharthRout I certainly will~ Thanks!

## @Gary's Student 2014-09-04 12:40:49

@SiddharthRout Nice approach! If the last used cell happened to contain only a

Commentand nothing else would we need twoFind()statements to locate it? Can theFind()method find either data or Comments??## @Siddharth Rout 2014-09-04 13:14:19

@Gary'sStudent: No. The above

`.Find`

will not find comments. For comments you can use`SpecialCells`

with`xlCellTypeComments`

:)## @tbur 2014-10-25 22:40:24

@SiddharthRout I hope you're making bags of money off of all this talent. :)

## @sancho.s 2014-12-24 13:52:24

I think that your description of

`UsedRange`

(it is highly) is misleading.unreliableto find the last cell which has data`UsedRange`

is simply not intended for that purpose, even though in some cases it may give the correct result. I think that the experiment proposed adds to the confusion. The result obtained with`UsedRange`

($A$1:$A$8) does not depend on first entering data an deleting it. The figure on the right will still be the same even without having entered data an deleted it. Please see my answer.## @user4317867 2015-01-22 23:39:28

What about the PowerShell spin on this VBA code? I've tried this

`$lastrow = $Worksheet.UsedRange.Rows.Count`

and`$lastcol = $Worksheet.UsedRange.Columns.Count`

along with`$range = $WorkSheet.Range($lastrow,$lastcol)`

which results in every row being selected.## @GUI Junkie 2015-12-22 16:27:39

I found an interesting bug in Excel2013. When the

`ActiveCell`

is in a table, the result is the last row of the table. To solve this, I assign the ActiveCell to a temporary range variable, then`Range("A1").Select`

, then find, then temporary range.select. Ugly, but effective.## @Trm 2016-04-18 13:26:48

It does seem to work for A clumn, but if I adjust range to C1 it always returns 1 as last row (not because of Else statement). So not sure what's wrong here

## @Trm 2016-04-18 13:35:27

Nevermind. Used formula for the sheet and not column

## @Jacob H 2017-08-28 19:38:08

Very helpful, even in 2017!

## @Duc Anh Nguyen 2017-09-01 01:26:16

to apply for last column then use this? ".Cells(1, .Columns.Count).End(xlToLeft).Column"

## @Siddharth Rout 2017-09-03 06:11:06

@DucAnhNguyen : Yes

## @user85489 2016-06-29 08:07:22

## @ZygD 2015-12-23 21:55:40

I created this one-stop function for

determining the last row, column and cell, be it for data, formatted (grouped/commented/hidden) cells or conditional formatting.Results look like this:

For more detailed results, some lines in the code can be uncommented:

One limitation exists - if there are tables in the sheet, results can become unreliable, so I decided to avoid running the code in this case:

## @ZygD 2016-02-09 04:36:50

@franklin - I've just noticed an inbox message with your correction which was rejected by reviewers. I corrected that mistake. I already used this function once when I needed and I will use it again, so really, huge thanks, my friend!

## @Bishop 2015-01-02 18:58:31

One important note to keep in mind when using the solution ...

... is to ensure that your

`LastRow`

variable is of`Long`

type:Otherwise you will end up getting OVERFLOW errors in certain situations in .XLSX workbooks

This is my encapsulated function that I drop in to various code uses.

## @Ashwith Ullal 2015-10-01 05:09:44

"here a65536 is last cell in the column a this code was tested on excel sti72003"200

and if u are using its "a1,048,576"

my code is just for the beginners to understand the concepts of what end(xlup) and other related commands can do

## @shoover 2015-10-01 19:14:33

Can you explain how your code answers this old question?

## @Kevin Brown 2015-10-02 21:48:56

While this answer is probably correct and useful, it is preferred if you include some explanation along with it to explain how it helps to solve the problem. This becomes especially useful in the future, if there is a change (possibly unrelated) that causes it to stop working and users need to understand how it once worked.

## @Ashwith Ullal 2015-12-13 04:50:38

I apologizes, i have corrected my mistake ....

## @dotNET 2015-04-27 15:21:54

I wonder that nobody has mentioned this, But the easiest way of getting the last used cell is:

This essentially returns the same cell that you get by

Ctrl+Endafter selecting Cell.`A1`

A word of caution: Excel keeps track of the most bottom-right cell that was ever used in a worksheet. So if for example you enter something in

B3and something else inH8and then later on delete the contents ofH8, pressingCtrl+Endwill still take you toH8cell. The above function will have the same behavior.## @shA.t 2015-04-28 04:28:05

`Last Cell`

in Excel sometimes refers to an empty cell (from`Used Range`

) that is different from`Last Used Cell`

;).## @shA.t 2015-04-28 05:06:27

The OP needed just the last row but you are right, last cell should be

H5; But you can test your function after deleting value inA5You will see that the last cell is that empty cell, and I think your code needs some edits like that`Cells(1,1).Select()`

is invalid it maybe is`ActiveSheet.Cells(1,1).Select`

; Also in VBA it's not recommended to use`Select`

;).## @Rachel Hettinger 2015-06-04 00:04:39

This breaks two cardinal rules for Excel VBA: Don't use Select! And don't assume sheet you want is the active one.

## @no comprende 2014-11-05 15:24:15

I would add to the answer given by Siddarth Rout to say that the CountA call can be skipped by having Find return a Range object, instead of a row number, and then test the returned Range object to see if it is Nothing (blank worksheet).

Also, I would have my version of any LastRow procedure return a zero for a blank worksheet, then I can know it is blank.