49

column A has data like this (ie frequent blank cells):

HEADING  <-- this is A1
kfdsl
fdjgnm
fdkj

gdfkj
4353

fdjk  <-- this is A9

I would like to be able to get the cell reference of the last cell that has data. So in the above example, I want to return: A9

I have tried this but it stops at the first blank cell (ie returning A4)

numofrows = destsheet.Range("A2").End(xlDown).Row - 1
Jaymin
  • 2,879
  • 3
  • 19
  • 35
pjj
  • 499
  • 1
  • 5
  • 4
  • 1
    possible duplicate of [How can I find last row that contains data in the Excel sheet with a macro?](http://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-the-excel-sheet-with-a-macro) – brettdj Dec 19 '13 at 09:23
  • Dim lastRow As Long Dim ws As Worksheet Set ws = Application.ActiveSheet With ws If WorksheetFunction.CountA(Cells) > 0 Then lastRow = Cells.Find(what:="*", SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row End If End With – Ronnie Royston Jun 14 '15 at 01:56

10 Answers10

49

I like this way:

ActiveSheet.UsedRange.Rows.Count

The same can be done with columns count. For me, always work. But, if you have data in another column, the code above will consider them too, because the code is looking for all cell range in the sheet.

Jaymin
  • 2,879
  • 3
  • 19
  • 35
Tomamais
  • 595
  • 3
  • 2
  • 17
    If you have no data in row 1, this will give the wrong answer - it will give the number of rows from the first to last cell. If you have data in cells a2:a4 this equation will result in `3`, not `4` (using Office 2010). – Floris May 28 '13 at 18:30
  • 6
    This method may be dangerous because it can returns a cell with color but no data or even a cell wich has contained data but as not been cleaned up properly. – Lucas Morin Jul 11 '13 at 15:08
  • 5
    This answer is wrong and should be deleted. `.UsedRange.Rows.Count` returns the *number of rows* in the UsedRange, which is not the same thing as the *row number* of the last piece of data. So if your rows 1 and 2 are empty, this will return the wrong answer by 2. – Jean-François Corbett Sep 16 '14 at 10:21
  • If you want to get the last row in the used range, do it properly -- see [newguy's answer](https://stackoverflow.com/a/35975280/1026). Read [sancho.s answer to know what this returns](https://stackoverflow.com/a/27637752/1026) and if you need a row that has **data** (and not some left-over formatting) and/or in a specific column, see [my solution](https://stackoverflow.com/a/49971492/1026). – Nickolay Apr 23 '18 at 00:15
43

Safest option is

Lastrow =  Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
Lastcol =  Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column

Don't use UsedRange or SpecialCells(xlLastCell) or End(xlUp). All these methods may give wrong results if you previously deleted some rows. Excel still counts these invisible cells.

These methods will work again if you delete your cells, save the workbook, close and re-open it.

nixda
  • 2,654
  • 12
  • 49
  • 82
  • 4
    +1 did a little testing and this does seem to be the most reliable especially if you don't know which column the data in the last row is – Graham Anderson Sep 26 '13 at 22:43
  • 4
    This is the only safe method to find the last cell containing data. Other methods fail with hidden rows/columns or cells with no data but formatting or cells that have been deleted. – Charles Williams Oct 21 '13 at 09:34
  • Should though use `Set` on a range,and test for that range to exist. This presumes data is present in the sheet - it will fail on a blank sheet – brettdj Dec 19 '13 at 09:16
  • So if I understand things correctly, I could use the "lastrow" variable for setting the first row to start pasting new data "Lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row" For example; "rnum = Lastrow +1" (This will be the first empty cell in the sheet(?)) – haakonlu Apr 07 '15 at 08:15
26

This will work, independent of Excel version (2003, 2007, 2010). The first has 65536 rows in a sheet, while the latter two have a million rows or so. Sheet1.Rows.Count returns this number dependent on the version. 

numofrows = Sheet1.Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row

or the equivalent but shorter

numofrows = Sheet1.Cells(Sheet1.Rows.Count,1).End(xlUp)

This searches up from the bottom of column A for the first non-empty cell, and gets its row number.

This also works if you have data that go further down in other columns. So for instance, if you take your example data and also write something in cell FY4763, the above will still correctly return 9 (not 4763, which any method involving the UsedRange property would incorrectly return).

Note that really, if you want the cell reference, you should just use the following. You don't have to first get the row number, and then build the cell reference. 

Set rngLastCell = Sheet1.Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp)

Note that this method fails in certain edge cases:

  • Last row contains data
  • Last row(s) are hidden or filtered out

So watch out if you're planning to use row 1,048,576 for these things!

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
  • 1
    This worked for me where `ActiveSheet.UsedRange.Rows.Count` has failed (which is what I usually use). – thornomad Nov 13 '12 at 19:57
  • You have a missing `.Row` on the great answer above. New users may be confused by the missing statement. `numofrows = Sheet1.Cells(Sheet1.Rows.Count,1).End(xlUp) ` as typed will provide value of last used cell. `Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row` with the `.Row` at end will provide the row # of last cell in col 1 as intended. – equalizer Dec 10 '15 at 16:42
19

I compared all possibilities with a long test sheet:

0,140625 sec for

lastrow = calcws.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).row

0 sec for

iLastRow = calcws.Cells(rows.count, "a").End(xlUp).row

and

numofrows = calcws.Cells.SpecialCells(xlLastCell).row

0,0078125 sec for

lastrow = calcws.UsedRange.rows.count
Do While 1
    If calcws.Cells(lastrow, 1).Value = "" Then
        lastrow = lastrow - 1
    Else
        Exit Do
    End If
Loop

I think the favourites are obvious...

sven
  • 223
  • 2
  • 2
  • 1
    +1 - this is my favorite answer. Not taking sides, just showing some of the options and their relative efficiency. Should have far more upvotes! – Floris Jul 05 '13 at 13:43
  • 8
    @Floris: Do you think speed is the most important criterion by which to judge which of various possibilities should be favourites? Rather than which works reliably? (Because they don't all return the same results in all circumstances...) – Jean-François Corbett Dec 19 '13 at 19:40
  • @jeanfrancoiscorbett - obviously getting the right answer matters most. I was commenting on the impartiality of this answer - an objective comparison of speeds . I liked that, and that is what I tried to say in my answer. I can see many ways in which things can go wrong (for example when the last row has a value in it...) – Floris Dec 19 '13 at 19:46
  • 3
    -1 I don't see a compilation of other codes as times as *an answer*, especially one that implies time (for an unspecified test) is more important that the actual answer reliability. Lastly `numofrows = calcws.Cells.SpecialCells(xlLastCell).row` applies to a sheet, not column A – brettdj Jan 11 '14 at 10:59
  • Indeed, performance comparison is useful, but the fact that all the faster methods return incorrect results under certain circumstances is not. In particular, the 4th snippet wrongly assumes that `calcws.UsedRange.rows.count` is the last row, but if the first few rows of the sheet are empty this assumption doesn't hold. – Nickolay Apr 20 '18 at 19:51
6

Dim RowNumber As Integer
RowNumber = ActiveSheet.Range("A65536").End(xlUp).Row

In your case it should return #9

Lubor
  • 69
  • 1
  • 1
5

Found this approach on another site. It works with the new larger sizes of Excel and doesn't require you to hardcode the max number of rows and columns.

iLastRow = Cells(Rows.Count, "a").End(xlUp).Row
iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column

Thanks to mudraker in Melborne, Australia

MonroeGA
  • 145
  • 1
  • 2
  • 8
3

These would both work as well, letting Excel define the last time it sees data 

numofrows = destsheet.UsedRange.SpecialCells(xlLastCell).row

numofrows = destsheet.Cells.SpecialCells(xlLastCell).row
Hari Seldon
  • 1,060
  • 2
  • 13
  • 27
  • 4
    This is wrong on a couple of levels. Your first suggestion, `.UsedRange.Rows.count`, will return the *number of rows* in the `UsedRange`, which is not the same thing as the *row number* of the last piece of data. So if your rows 1 and 2 are empty, this will return the wrong answer by 2. Also, this includes the last non-empty cell on the entire sheet, not just the column under consideration. Maybe that's what the OP wants, but I don't really think so; plus, it's really susceptible to error if someone accidentally writes something in cell "FY54239". – Jean-François Corbett Jun 10 '11 at 09:18
  • Your second suggestion, `destsheet.SpecialCells(xlLastCell).row` doesn't even compile, at least in Excel 2003: `.SpecialCells` doesn't apply to the Sheet object. – Jean-François Corbett Jun 10 '11 at 09:19
  • 1
    @Jean... It is actually `destsheet.Cells.SpecialCells(xlLastCell).Row` ... That is definitely my bad for not including that. To your first point the `.SpecialCells(xlLastCell).Row` can be applied to `UsedRange` as well. Ive edited my response with working code. – Hari Seldon Jun 10 '11 at 13:05
2
  n = ThisWorkbook.Worksheets(1).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
Dado
  • 1,016
  • 1
  • 12
  • 20
1

I prefer using the CurrentRegion property, which is equivalent to Ctrl-*, which expands the current range to its largest continuous range with data. You start with a cell, or range, which you know will contain data, then expand it. The UsedRange Property sometimes returns huge areas, just because someone did some formatting at the bottom of the sheet.

Dim Liste As Worksheet    
Set Liste = wb.Worksheets("B Leistungen (Liste)")     
Dim longlastrow As Long
longlastrow = Liste.Range(Liste.Cells(4, 1), Liste.Cells(6, 3)).CurrentRegion.Rows.Count
Jaymin
  • 2,879
  • 3
  • 19
  • 35
Andrew Magerman
  • 1,394
  • 1
  • 13
  • 23
1

For greater clarity, I want to add a clear example and running

            openFileDialog1.FileName = "Select File"; 
            openFileDialog1.DefaultExt = ".xls"; 
            openFileDialog1.Filter = "Excel documents (.xls)|*.xls"; 


            DialogResult result = openFileDialog1.ShowDialog();


            if (result==DialogResult.OK)
            {

                string filename = openFileDialog1.FileName;


                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;

                xlApp = new Excel.Application();
                xlApp.Visible = false;
                xlApp.DisplayAlerts = false;



                xlWorkBook = xlApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                var numRows = xlWorkSheet.Range["A1"].Offset[xlWorkSheet.Rows.Count - 1, 0].End[Excel.XlDirection.xlUp].Row;

                MessageBox.Show("Number of max row is : "+ numRows.ToString());

                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();

            }
daniele3004
  • 13,072
  • 12
  • 67
  • 75