I am trying to make this way of finding the last row as I found the last column:
I know how to do it this way but it is not as helpful as the prior would be:
To get the Last Row in the Worksheet UsedRange we need to use the UsedRange property of an VBA Worksheet. 'Get Last Row in Worksheet UsedRange Dim lastRow as Range, ws As Worksheet Set ws = ActiveSheet Debug.Print ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row Set lastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).EntireRow.
I tried doing this:
Any help would be greatly appreciated.
Synopsis:Would like the below way again for last row. Thanks
3 Answers
You should use a with statement to qualify both your Rows
and Columns
counts. This will prevent any errors while working with older pre 2007 and newer 2007 Excel Workbooks.
Last Column
Last Row
Or
This is the best way I've seen to find the last cell.
One of the disadvantages to using this is that it's not always accurate. If you use it then delete the last few rows and use it again, it does not always update. Saving your workbook before using this seems to force it to update though.
Using the next bit of code after updating the table (or refreshing the query that feeds the table) forces everything to update before finding the last row. But, it's been reported that it makes excel crash. Either way, calling this before trying to find the last row will ensure the table has finished updating first.
Another way to get the last row for any given column, if you don't mind the overhead.
Not the answer you're looking for? Browse other questions tagged excelvba or ask your own question.
OK, many times we have to find the last Row or sometimes the last Column in a sheet, and we think, OK the formula:
range('A'&rows.Count).End(xlUp).Row or: cells(rows.Count,1).end(xlup).row
and
cells(1,columns.Count).end(xltoleft).column
work just fine, but what if last data isn't in column A? how many Columns do i have to look for...
Same if i want to find last column...
Therefor I wrote LastRowOrColumn()
Syntax: LastRowOrColumn( [LstDt=10] , [FstDt=1] , [RowOrColumn=true] ) ,all arguments are optional with default values as shown
LstDt = Up to which Column or Row look for: when searching maxRows then LstDt refers to columns and vice versa
FstDt = From which Column or Row start looking: when searching maxRows then FstDt refers to columns and vice versa
RowOrColumn = True (default) to look for Rows, the most common, or False to look for columns...
ex:
LastRowOrColumn >>Searches Columns 1-10 and returns max Rows
LastRowOrColumn(1000) >> Searches Columns 1-1000 and returns max Rows
LastRowOrColumn(1000,100) >> Searches Columns 100-1000 and returns max Rows
LastRowOrColumn(,false) >> Searches Rows 1-10 and returns max Columns
LastRowOrColumn(1000,100,false) >> Searches Rows 100-1000 and returns max Columns
LastRowOrColumn(rowclmnfl:=false) >> Searches Rows 1-10 and returns max Columns
and so on. Results are very fast (for 10000 columns<1sec) so there are no excuses any more... Keep programming mates
and the code:
Function LastRowOrColumn(Optional ByVal LstDt As Long = 10, Optional ByVal FstDt As Long = 1, Optional RowClmnFL As Boolean = True)
Dim tmpLng As Long, maxDt As Long, IDt As Long, EndDt As Long
Application.ScreenUpdating = False
If LstDt < FstDt Then tmpLng = LstDt: LstDt = FstDt: FstDt = tmpLng
If FstDt < 1 Then FstDt = 1
If LstDt < FstDt Then LstDt = 10
If RowClmnFL Then EndDt = Rows.Count Else EndDt = Columns.Count
maxDt = 0
For IDt = FstDt To LstDt
If RowClmnFL Then
tmpLng = Cells(EndDt, IDt).End(xlUp).Row
Else
tmpLng = Cells(IDt, EndDt).End(xlToLeft).Column
End If
If tmpLng > maxDt Then maxDt = tmpLng
Next
LastRowOrColumn = maxDt
End Function
any suggestions or comments?