0

I have an issue with iLastRow in VBA. I use the following tag to define last row with data in a column:

iLastRow = Cells(Rows.Count, "H").End(xlUp).Row

However I always get the last row with data at all as a result. So if the last row with data in column A is row 12 and the last row with data in column H is 10, I still get 12 as a result. For my purposes I would need the code to give me 10 as a result. Any help is highly appreciated, thanks!

Chrismas007
  • 6,085
  • 4
  • 24
  • 47
Jan
  • 13
  • 4
  • possible duplicate of [Error Finding Last Used cell In VBA](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) – RubberDuck Jan 12 '15 at 17:08
  • check your line of code, if you get the last row from column A , then it means you wrote "A" instead of "H". to avoid having the wrong workbook or worksheet active, use references `thisworkbook.sheets("Sheet1").cells(Rows.Count, "H").End(xlUp).Row` , for example. – Patrick Lepelletier Jan 13 '15 at 16:16

4 Answers4

3

Your posted code should work if the correct worksheet is active.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
1

This should works:

iLastRow = Range("H" & Rows.Count).End(xlUp).Row

I'd suggest to write custom function:

Function GetLastRow(ByVal wsh as Worksheet, Optional sColName As String = "A") As Integer
    GetLastRow = wsh.Range(sColName & wsh.Rows.Count).End(xlUp).Row
End Function

Usage:

iLastRow = GetLastRow(ActiveSheet, "H")

Cheers,
Maciej

Maciej Los
  • 8,468
  • 1
  • 20
  • 35
  • Why would your write a custom function that takes up 4 lines of code, rather than just the one line. – Chrismas007 Jan 12 '15 at 16:34
  • 2
    @Chrismas007 because you're going to use this function all the time, and in many different projects. Having a standard function that you can copy from one file to another, and which you can call from any subroutine/procedure, is a good practice. – David Zemens Jan 12 '15 at 16:36
  • 2
    @Chrismas007, there's a tons of reason. First: the code is executed in context, second: it provides extensions, like another column ;) – Maciej Los Jan 12 '15 at 16:39
  • @DavidZemens I would agree with you if this took anymore than one line of code. Time yourself copying and pasting this custom function from one project to the next and then writing the syntax code to call the function... doubt you could ever beat the time of writing just the `Sheet.Range.End.Row` syntax. – Chrismas007 Jan 12 '15 at 16:46
  • 2
    @Chrismas007, it's not a matter of time of writing code, but it's a matter of good programming practice. Let say, you need to find last row several time in different modules. What is better: hard-coding one line of code or use custom function, which provides a way to find last row without activating sheet. So many times i saw code that causes crashes, because of code without context usage. – Maciej Los Jan 12 '15 at 16:59
  • 2
    I would actually use an even longer function, which Siddharth Rout explains [here](http://www.siddharthrout.com/2012/10/02/find-last-row-in-an-excel-sheetvbavb-net/) (the second function). The reasons are numerate. I may need to find the "last row" in dozens of procedures. Having a *single* function to do this reduces the risk of error, reduces the need to modify dozens of lines of code (if anything in my requirements changes in the future, etc.). There are also instances where `.End(xlUp)` etc., do not provide correct results. And, as @MaciejLos mentions: it provides helpful extensions... – David Zemens Jan 12 '15 at 17:00
0

So far none of the proposed solutions works for me unfortunately. Could it be that it has something to do with the fact that I have formatted the area as a table? So I would always return the last row of the table even if the cell in this column is empty...?

Jan
  • 13
  • 4
0

Found a working solution for this particular case:

iLastRow = Range("H1").End(xlDown).Row + 1

Jan
  • 13
  • 4