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?
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
LastRow = ActiveSheet.UsedRange.Rows.Count
ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.count).row
ActiveSheet can be replaced with WorkSheets(1)
or WorkSheets("name here")
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
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
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
Better:
if cells(i,1)="" then
nextEmpty=i:
exit for
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.
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.
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
Source: Stackoverflow.com