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

User · Accepted Answer

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

User · Answer

Better    if cells i 1     then  nextEmpty i   exit for

User · Answer

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

User · Answer

LastRow   ActiveSheet UsedRange Rows Count

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

ActiveSheet UsedRange Rows ActiveSheet UsedRange Rows count  row   ActiveSheet can be replaced with WorkSheets 1  or WorkSheets  name here

User · Answer

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

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

Examples related to excel

Examples related to vba