how to know the last row filled in vba (excel)?

Question

I have a sheet calls "Recap" and I want to know how much line that I have in this sheet.I tried with this code:

Function FindingLastRow(Mysheet As String) As Long

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ThisWorkbook.Worksheets(Mysheet) 
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
FindingLastRow = LastRow

End Function

......

in my macro i tried this:

....

Dim lastR As Long
lastR=FindingLastRow("Recap")
msgBox lastR

.....

You have not passed the argument to the `FindingLastRow` method. You have nit declared what `MySheet` is. — PaulFrancis, Apr 15 '15 at 14:57
@memed (upped) question as it got some interesting responses — whytheq, Apr 15 '15 at 16:04

score 2 · Accepted Answer · 2015-04-15T15:44:46.370

2

You are passing a string into your FindingLastRow function but are not using it. shiit is the parameter being passed in but you later try to use something called Mysheet.

Function FindingLastRow(Optional MySheet As String, Optional sCOL as String = "A") As Long
    Dim sht As Worksheet
    Dim LastRow As Long

    If Not CBool(Len(MySheet)) Then MySheet = ActiveSheet.Name

    Set sht = ThisWorkbook.Worksheets(MySheet)
    LastRow = sht.Cells(sht.Rows.Count, sCOL).End(xlUp).Row
    FindingLastRow = LastRow
    Set sht = nothing
End Function

Sub test_FindingLastRow()
    Dim lastR As Long
    lastR = FindingLastRow
    MsgBox lastR
    lastR = FindingLastRow("Recap")
    MsgBox lastR
    lastR = FindingLastRow("Recap", "B")
    MsgBox lastR
End Sub

If no worksheet name is passed in then the currently ActiveSheet's name is used. If no alphabetic column name is passed in it will use column A.

edited Apr 15 '15 at 15:44

answered Apr 15 '15 at 15:05

Your code works very well, thank you so much ! pls you can explain to me what he is doing the `If Not CBool(Len(MySheet)) Then MySheet = ActiveSheet.Name`?? – memed Apr 15 '15 at 15:18
1

@memed the argument `MySheet` is declared as `Optional` so you can run a line of code like this `lastR = FindingLastRow()` without the argument - that line you mention will then fill in the gaps - and the code will still run. I think a nicer optional argument might be for column - as you might like to not always find the last row in column "A". – whytheq Apr 15 '15 at 15:20
@whythea yes it true that's what I did. Now I understand better. thank you very much :) – memed Apr 15 '15 at 15:39
@whytheq - Good point. I've adjusted the function. – Apr 15 '15 at 15:45
@Jeeped cool - didn't realise you could shortcut like that in vba `...="A"` ..like it. Am I correct in thinking if you make the optional variables type `Variant` then you can use the method `IsMissing` ? – whytheq Apr 15 '15 at 15:49
(@Jeeped you could always up my answer by way of a thank you ...) – whytheq Apr 15 '15 at 15:52
@whytheq - a) You can set a default for an optional parameter like that but it has to be a constant. The `ActiveSheet.Name` is not a constant so it had to be dealt with differently. b) Yes, only a variant type parameter will respond correctly to the `IsMissing` function. Also, optional variant parameters do not discount macros from being listed in the Alt+F8 Macros dialog. Any other var type of optional parameter will ensure that the macro is not listed. Sometimes I even use one that I will never actually use just so it is not listed. – Apr 15 '15 at 15:58
@Jeeped what is the problem with it being listed? `IsMissing` is a nice self-documenting method compared to say `If Not CBool(Len(MySheet))...` – whytheq Apr 15 '15 at 16:03
1

@whytheq - a) Sometimes I write short helper routines that I do not want people who don't know exactly what the routine might do fooling with. b) Lots of secondary subs may not actually need parameters passed into them in order to function properly but having them listed only serves to obfuscate the ones that are important and you never know when someone will run something just to see what it does. c) while I'm a firm believer that 'if you build something idiot-proof, the world will build a better idiot', I don't like to tempt fate. – Apr 15 '15 at 16:09
'if you build something idiot-proof, the world will build a better idiot' that needs to go up on a wall somewhere! – SierraOscar Apr 15 '15 at 16:13
@Jeeped I'm nicking that!! for here....http://stackoverflow.com/users/1179880/whytheq?tab=profile – whytheq Apr 15 '15 at 16:16

whytheq · Answer 2 · 2015-04-15T15:26:31.260

If you want to find the last cell in a book other than ThisWorkbook:

Function FindingLastRow( _
  ByVal shtName As String, _
  Optional ByVal colLetter As Variant, _
  Optional ByRef wkBk As Variant _
  ) As Long

  Dim colId As String
  If IsMissing(colLetter) Then
     colId = "A"
  Else
     colId = colLetter
  End If

  Dim myTargBk As Excel.Workbook
  If IsMissing(wkBk) Then
     Set myTargBk = ThisWorkbook
  Else
     Set myTargBk = wkBk
  End If

  Dim sht As Worksheet
  Set sht = myTargBk.Worksheets(shtName)
  With sht
    FindingLastRow = .Cells(.Rows.Count, colId).End(Excel.xlUp).Row
  End With

End Function

Used like this:

Sub findLast()
  MsgBox FindingLastRow("Sheet1")
End Sub

Or to find the last row of column A in a different open workbook...

Sub findLast2()
  Dim w As Excel.Workbook
  Set w = Excel.Workbooks("Norf.xlsx")
  MsgBox FindingLastRow("Sheet1", , w)
End Sub

Or to find the last row of column B in a different open workbook...

Sub findLast3()
  Dim w As Excel.Workbook
  Set w = Excel.Workbooks("Norf.xlsx")
  MsgBox FindingLastRow("Sheet1", "B", w)
End Sub

SierraOscar · Answer 3 · 2015-04-15T15:47:44.580

1

You're also assuming that the last cell will be in column A which may not be the case, so I tend to revert to something like this:

Function FindingLastRow(sheetName As String) As Long
    FindingLastRow = Sheets("sheetName").Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row 
End Function

edited Apr 15 '15 at 15:47

answered Apr 15 '15 at 15:19

SierraOscar

17,507
6
40
68

Byron Wall · Answer 4 · 2015-04-15T15:24:45.880

0

The UsedRange on a Worksheet variable is very helpful here. You really don't need a UDF to get the row count.

LastRow = Worksheets("Recap").UsedRange.Rows.Count

This method only works if your data starts in row 1 and the sheet does not have formatting outside of the data. You could add in the starting row + UsedRange.Cells(1,1).Row if you know the data starts somewhere other than row 1. The second issue prevents the use of UsedRange.

edited Apr 15 '15 at 15:24

answered Apr 15 '15 at 15:18

Byron Wall

3,970
2
13
29

The used range may not necessarily contain data though and is only updated on the workbook being saved – SierraOscar Apr 15 '15 at 15:20
@SOofWXLS, you are correct about the UsedRange possibly not containing data. It is updated with changes though without saving. – Byron Wall Apr 15 '15 at 15:23
I have to disagree with that - the UsedRange doesn't always keep a good track of what the used range is. Typically I can't get it to do that at the moment. Excel doesn't always return the correct last cell with UsedRange - it remembers cells that have had values/formatting which have subsequently been cleared. http://blog.contextures.com/archives/2012/03/01/select-actual-used-range-in-excel-sheet/ – Darren Bartrup-Cook Apr 15 '15 at 15:28
@DarrenBartrup-Cook, I am pretty confident it properly tracks, but it is easy to not realize a cell still has info in it. I generally make sure to ALT+H+E+A (clear all) if I think there is residual info in a cell. Even better is to just delete the rows/columns if the sheet allows it. In some cases, the `UsedRange` is a good way to find out if cells outside the "data" have not been sanitized and cleared of formatting if they appear to be good. – Byron Wall Apr 15 '15 at 15:36
Subject to answering an earlier Excel question today I set up some sample data starting in row 42 going down a few rows. The `.UsedData` is reported as `S42:AK44` so rows.count isn't going to be accurate. Then I threw a *Long Date* number format on some blank cells in O36:O69. The `.UsedRange` is now reported as `O36:AK69`. Saving does not reset these. – Apr 15 '15 at 16:17
@Jeeped, I caught the same issue with the `UsedRange` not starting in row 1. I added a blurb earlier about adding in `UsedRange.Cells(1,1).Row` to get the correct row number. The formatted cells being included is a legitimate issue and could show up in real spreadsheets. – Byron Wall Apr 15 '15 at 16:24
Over UsedRange I'd use the Find method that @SOofWXLS has given as an answer - I just have a .bas file that I drag and drop into my project with a similar function. – Darren Bartrup-Cook Apr 16 '15 at 14:16

how to know the last row filled in vba (excel)?

4 Answers4

Linked