How to determine the last Row used in VBA including blank spaces in between

Question

How can I determine the last row in an Excel sheet, including some blank lines in the middle?

With this function:

Function ultimaFilaBlanco(col As String) As Long        
        Dim lastRow As Long
        With ActiveSheet
            lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, col).End(xlUp).row
        End With            
        ultimaFilaBlanco = lastRow          
End Function

And this data:

Row 1 : Value
Row 2 : Value
Row 3 : Value
Row 4 : Value
Row 5 : Value
Row 6 : White
Row 7 : Value
Row 8 : Value
Row 9 : Value
Row 10  : White
Row 11  : White
Row 12  : White
Row 13  : Value
Row 14  : White

The function returns 5, and I need 13. Any idea how to do it?

Duplicate of [Last not empty cell in row; Excel VBA](http://stackoverflow.com/questions/4872512/last-not-empty-cell-in-row-excel-vba) — brettdj, Dec 07 '12 at 13:02
See my post at https://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-the-excel-sheet-with-a-macro/46419169#46419169 I think it can solve your request. — Phaithoon Jariyanantakul, Sep 26 '17 at 06:32
Strangely enough, I'm running the following code and it works even with blank rows in between: `ActiveSheet.Cells(ActiveSheet.Rows.count, 3).End(xlUp).Row` _(Excel 2016 on MacOS X)_ — KJH, Mar 05 '18 at 17:38
Be careful, the `UsedRange.Rows.Count` returns the number of rows used **ever**. If you use 100 rows and then `clearcontents` the `UsedRange.Row.Count` still returns 100. — Bill, Jun 23 '14 at 15:26

Abdelhameed Mahmoud · Answer 1 · 2013-06-14T11:30:30.837

60

The best way to get number of last row used is:

ActiveSheet.UsedRange.Rows.Count for the current sheet.

Worksheets("Sheet name").UsedRange.Rows.Count for a specific sheet.

To get number of the last column used:

ActiveSheet.UsedRange.Columns.Count for the current sheet.

Worksheets("Sheet name").UsedRange.Columns.Count for a specific sheet.

I hope this helps.

Abdo

edited Jun 14 '13 at 11:30

answered Jun 12 '13 at 14:19

Abdelhameed Mahmoud

2,118
2
22
17

1

Thanks. This works FASTER as I know. I'd accept this as answer. – Jet May 04 '14 at 10:38
5

This only works if the first non-blank row is row 1. – trincot Jun 13 '16 at 19:41
If you want to get the last row in the used range, do it properly -- see [manuel's answer here](https://stackoverflow.com/a/20745570/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, check out [my solution](https://stackoverflow.com/a/49971492/1026). – Nickolay Apr 23 '18 at 00:31

score 27 · Answer 2 · answered Dec 23 '13 at 14:30

27

ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Rows(1).Row -1

Short. Safe. Fast. Will return the last non-empty row even if there are blank lines on top of the sheet, or anywhere else. Works also for an empty sheet (Excel reports 1 used row on an empty sheet so the expression will be 1). Tested and working on Excel 2002 and Excel 2010.

answered Dec 23 '13 at 14:30

manuel

371
3
3

1

This surely has to be the most accurate answer. The Count answers assume you are starting from the first row (ie 1) - there are occasions where you are not. This gives you the actual row index the range ends on. I would accept this as the answer. – Andez Nov 11 '14 at 15:08
@manuel your statement did the job perfectly for starting blank rows, can u elaborate it a little – BiLaL Apr 10 '17 at 11:07
While this is the correct way to get the last row of the `UsedRange`, it's important to understand [what it is (and isn't)](https://stackoverflow.com/a/27637752/1026). – Nickolay Apr 23 '18 at 00:35

score 18 · Answer 3 · answered Dec 03 '12 at 17:24

18

I use the following:

lastrow = ActiveSheet.Columns("A").Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row

It'll find the last row in a specific column. If you want the last used row for any column then:

lastrow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row

answered Dec 03 '12 at 17:24

Sid Holland

2,871
3
27
43

More reliable... Works fine even with `ClearContents` or removing duplicates – Pugazh Apr 24 '15 at 09:54

score 15 · Accepted Answer · answered Dec 03 '12 at 16:18

15

You're really close. Try using a large row number with End(xlUp)

Function ultimaFilaBlanco(col As String) As Long

        Dim lastRow As Long
        With ActiveSheet
            lastRow = ActiveSheet.Cells(1048576, col).End(xlUp).row
        End With

        ultimaFilaBlanco = lastRow

End Function

answered Dec 03 '12 at 16:18

fbonetti

6,652
3
34
32

Hey Thanks Im testing, Do you know why I have to return a variant and not a long? – Jonathan Raul Tapia Lopez Dec 04 '12 at 09:29
Check @JHK answer for improvement. – Brethlosze Jan 20 '19 at 07:02

score 4 · Answer 5 · edited Feb 13 '17 at 13:56

4

LastRow = ActiveSheet.UsedRange.Rows.Count

edited Feb 13 '17 at 13:56

CallumDA

12,025
6
30
52

answered Dec 03 '12 at 16:19

jtorreon

49
2

3

That function made exactly the same that my function. – Jonathan Raul Tapia Lopez Dec 04 '12 at 09:36
This is a duplicate of [an answer that was posted later but gathered many more upvotes](https://stackoverflow.com/a/17067939/1026) and shares the same problems, namely it returns the number of rows in the UsedRange, not the [last row number](https://stackoverflow.com/a/20745570/1026), it also doesn't mention [what the UsedRange is](https://stackoverflow.com/a/27637752/1026). – Nickolay Apr 23 '18 at 00:39

score 3 · Answer 6 · answered Jun 14 '13 at 12:03

The Problem is the "as string" in your function. Replace it with "as double" or "as long" to make it work. This way it even works if the last row is bigger than the "large number" proposed in the accepted answer.

So this should work

Function ultimaFilaBlanco(col As double) As Long        
    Dim lastRow As Long
    With ActiveSheet
        lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, col).End(xlUp).row
    End With            
    ultimaFilaBlanco = lastRow          
End Function

JHK · Answer 7 · 2018-06-21T08:20:04.743

3

If sheet contains unused area on the top, UsedRange.Rows.Count is not the maximum row.

This is the correct max row number.

maxrow = Sheets("..name..").UsedRange.Rows(Sheets("..name..").UsedRange.Rows.Count).Row

edited Jun 21 '18 at 08:20

answered Jun 21 '18 at 05:26

JHK

64
5

jainashish · Answer 8 · 2017-08-05T17:34:25.033

Well apart from all mentioned ones, there are several other ways to find the last row or column in a worksheet or specified range.

Function FindingLastRow(col As String) As Long

  'PURPOSE: Various ways to find the last row in a column or a range
  'ASSUMPTION: col is passed as column header name in string data type i.e. "B", "AZ" etc.

   Dim wks As Worksheet
   Dim lstRow As Long

   Set wks = ThisWorkbook.Worksheets("Sheet1") 'Please change the sheet name
   'Set wks = ActiveSheet   'or for your problem uncomment this line

   'Method #1: By Finding Last used cell in the worksheet
   lstRow = wks.Range("A1").SpecialCells(xlCellTypeLastCell).Row

   'Method #2: Using Table Range
   lstRow = wks.ListObjects("Table1").Range.Rows.Count

   'Method #3 : Manual way of selecting last Row : Ctrl + Shift + End
   lstRow = wks.Cells(wks.Rows.Count, col).End(xlUp).Row

   'Method #4: By using UsedRange
   wks.UsedRange 'Refresh UsedRange
   lstRow = wks.UsedRange.Rows(wks.UsedRange.Rows.Count).Row

   'Method #5: Using Named Range
   lstRow = wks.Range("MyNamedRange").Rows.Count

   'Method #6: Ctrl + Shift + Down (Range should be the first cell in data set)
   lstRow = wks.Range("A1").CurrentRegion.Rows.Count

   'Method #7: Using Range.Find method
   lstRow = wks.Column(col).Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row

   FindingLastRow = lstRow

End Function

Note: Please use only one of the above method as it justifies your problem statement.

Please pay attention to the fact that Find method does not see cell formatting but only data, hence look for xlCellTypeLastCell if only data is important and not formatting. Also, merged cells (which must be avoided) might give you unexpected results as it will give you the row number of the first cell and not the last cell in the merged cells.

In [my answer to a similar question](https://stackoverflow.com/a/49971540/1026) I list the gotchas with most of these approaches... — Nickolay, Apr 23 '18 at 00:40

score 1 · Answer 9 · answered Jan 29 '18 at 21:48

1

ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.count).row

ActiveSheet can be replaced with WorkSheets(1) or WorkSheets("name here")

answered Jan 29 '18 at 21:48

18C

2,014
10
16

score -1 · Answer 10 · answered Sep 14 '15 at 08:51

here is my code to find the next empty row for example in first row 'A'. To use it for any other row just change cells(i,2 or 3 or 4 so on)

Sheets("Sheeet1").Select
   for i=1 to 5000
        if cells(i,1)="" then nextEmpty=i goto 20
   next i
20 'continue your code here 

enter code here

score -1 · Answer 11 · edited Dec 12 '17 at 15:17

-1

Better:

if cells(i,1)="" then 
nextEmpty=i: 
exit for

edited Dec 12 '17 at 15:17

Vagish

2,520
19
32

answered Dec 12 '17 at 13:43

MarcoP

11

Bit late to the party.... although I agree that `Exit For` would be better than `GoTo 20` - I assume you're responding to the answer given by @OdiljonJakhangirov. But - code for `Ctrl+Up` is faster as is the `Find` method. – Darren Bartrup-Cook Dec 12 '17 at 16:16

How to determine the last Row used in VBA including blank spaces in between

11 Answers11

Linked

Related