2008-09-16 10:54:53 8 Comments

How can I find the last row that contains data in a specific column and on a specific sheet?

### Related Questions

#### Sponsored Content

#### 7 Answered Questions

#### 5 Answered Questions

#### 2 Answered Questions

#### 2 Answered Questions

#### 2 Answered Questions

#### 1 Answered Questions

#### 2 Answered Questions

#### 2 Answered Questions

### Excel Macro to Find Duplicate in Sheet A and Delete Duplicate in Sheet B

**2014-08-12 22:44:15****brandon****2255**View**-1**Score**2**Answer- Tags: excel vba excel-vba duplicates

## 12 comments

## @Fionnuala 2008-09-16 11:16:30

How about:

Re Comment

This

Will return the row number of the last cell even when only a single cell in the last row has data.

## @GSerg 2008-09-16 11:30:48

This function will return wrong results when: - strColumn is a number - there is some data in row 65536 - you are using Excel 2007 with more than 65536 rows

## @JimmyPena 2012-06-13 00:31:58

@GSerg You can correct the code, then delete the comment if you like (since it won't apply any longer <g>)

## @Scott Marcus 2016-03-13 20:22:20

I believe this will incorrectly report which cell is the last one if the cell had data and then the data was removed.

## @Nickolay 2018-04-22 23:48:32

Siddharth Rout's answer to "Error in finding last used cell in VBA" elaborates on these two approaches, noting some problems e.g. with using

`Rows.Count`

(see "Find Last Row in a Column" and "Find Last Row in a Sheet"); my answer to that question lists some possibly unintended consequences of using these (namely, not working with autofilter and`.Find`

messing with the defaults in the Excel's Find dialog box).## @Nickolay 2018-04-22 22:53:29

All the solutions relying on built-in behaviors (like

`.Find`

and`.End`

) have limitations that are not well-documented (see my other answer for details).I needed something that:

non-emptycell (i.e. that hasany formula or value, even if it's an empty string) in aspecific column`Worksheet_Change`

handler without feeling sluggish)The solution below:

`UsedRange`

to find the upper bound for the row number (to make the search for the true "last row" fast in the common case where it's close to the end of the used range);`UsedRange`

we need to skip)(No tests, sorry)

## @Phaithoon Jariyanantakul 2017-09-26 06:21:50

sheet.UsedRange.Rows.Count: retrurn number of rows used, not include empty row above the first row used

if row 1 is empty, and the last used row is 10, UsedRange.Rows.Count will return 9, not 10.

This function calculate the first row number of UsedRange plus number of UsedRange rows.

## @Nickolay 2018-04-23 00:07:03

I feel this largely duplicates the other answer by newguy. Also note that using

`Integer`

instead of`Long`

for the row numbers risks running into an`Overflow`

error with sheets larger than 65k rows.## @newguy 2016-03-13 20:16:06

I would like to add one more reliable way using

`UsedRange`

to find the last used row:Similarly to find the last used column you can see this

Result in Immediate Window:

## @micstr 2016-03-17 12:18:51

Note

`UsedRange`

will also pick up formulas which may be a problem if you have formulae dragged down below visible data (or even formatting).## @newguy 2016-03-25 04:58:09

@micstr True but so does every other method will do that.

## @Ashwith Ullal 2015-10-30 11:08:29

This is look for value in column A because of "A65536"

## @user2988717 2014-08-26 15:14:39

Simple and quick:

Example use:

## @user2988717 2016-05-23 19:20:10

Or like this 'function getSheetLastRow(sheet2Check as worksheet) lastRow = sheet2Check .Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row getSheetLastRow=lastRow end function'

## @Nickolay 2018-04-23 00:02:28

This isn't limited to a specific column as the question requested; it also uses

`Select`

and accesses`Range`

/`Cells`

without specifying the worksheet object, which is considered bad style. The "Find Last Row in a Sheet" section of Siddharth Rout's answer to "Error in finding last used cell in VBA" has a better solution, if you feel you must use`.Find`

...## @Dick Kusleika 2008-09-16 16:34:08

Usage:

`?lastdata(activecell.EntireColumn).Address`

## @Jon Fournier 2008-09-16 15:21:00

You should use the

`.End(xlup)`

but instead of using 65536 you might want to use:That way it works for Excel 2007 which I believe has more than 65536 rows

## @Greg Podesta 2009-06-07 18:53:01

Here's a solution for finding the last row, last column, or last cell. It addresses the A1 R1C1 Reference Style dilemma for the column it finds. Wish I could give credit, but can't find/remember where I got it from, so "Thanks!" to whoever it was that posted the original code somewhere out there.

## @databyss 2008-09-16 11:23:04

First function moves the cursor to the last non-empty row in the column. Second Function prints that columns row.

## @Jon Crowell 2012-06-02 23:13:23

This doesn't work if there are any blank cells in the column.

## @GSerg 2008-09-16 11:14:22

Usage:

## @gdelfino 2015-03-26 14:33:54

Is the check for isempty(r.value) really needed? Shouldn't it always have a value?

## @GSerg 2015-03-26 14:57:39

@gdelfino Yes it is. E.g. column A has values in rows 1-10, column B has values in rows 1-8.

`UsedRange`

will be`A1:B10`

, the intersection with`B:B`

will be`B1:B10`

, the last cell is`B10`

and it is empty.## @Nickolay 2018-04-22 23:57:21

Why the extra complexity of getting the last row of

`UsedRange`

instead of starting with the very last row of the sheet (`sht.Rows.Count`

) if you use`.end(xlup)`

anyway? I believe the way you're getting the last row will fail if the UsedRange doesn't start at the first row (i.e. if the first row is empty).## @GSerg 2018-04-23 00:23:43

@Nickolay Because starting with the very last row of the sheet is based on the false assumption that the last row of the sheet is empty. It often is, but not always. I am not happy with false assumptions like that. It will jump up past the original

`UsedRange`

only if the column is completely empty, which is correct behaviour given the only two variables that define the list here are the worksheet and the column number, which is exactly how the OP worded the problem.## @Nickolay 2018-04-23 00:27:02

No, that's the argument in favor of the

`if isempty(r.value)`

check -- I applaud that. You could start with the very last row, still make that check, but save 5 lines before that (while removing the wrong counting logic) -- and end up with a more robust version of "Find Last Row in a Column" section of Siddharth Rout's answer to "Error in finding last used cell in VBA" (still not dealing with autofilter, but it's OK for some use-cases). Unless this is an optimization, which I wouldn't think is necessary, but I thought I would ask first.## @GSerg 2018-04-23 00:43:26

@Nickolay It was almost ten years ago that I wrote that code. I can't tell you exactly what I had in mind back then, but from the looks of it, because the function is supposed to return the last unusable row (to which you add 1 to get the first usable row), I made it to return 0 in case the column in question is not a part of

`UsedRange`

and so is completely empty - zero then is a valid answer because you add + 1 and end up writing the new data to the first row, which is correct, and in that case`.End`

is not called.## @GSerg 2018-04-23 00:44:11

@Nickolay Then again, if the column is completely empty, but

isa part of`UsedRange`

, it will return 1 as opposed to 0, which is not consistent, so you have a point.## @Galwegian 2008-09-16 10:56:52