1

Here's my use-case:

  • In ws "Z" I would like a given cell to retrieve the content of the last (non-empty) cell in ws "A" for column "C".
  • I would like also that, when I add a new row in ws "A" (column "C" for instance), the cell in ws "Z" gets automatically updated.

More concretely, ws "A" contains an history table with revision numbers in column "C" and when a new entry is added, I wish the cell in ws "Z" to reflect this new value automatically.

I'm thinking about using a formula to be written in VBA. So somewhere on SO I found something (originally a macro) that I converted into a function:

Function GetLastRow(strSheet, strColum) As String

Dim MyRange As Range

Set MyRange = Worksheets(strSheet).Range(strColum & "1")

GetLastRow = Cells(65536, MyRange.Column).End(xlUp).Value

End Sub

Using Excel 2003, in ws "Z", I have this cell that has this formula: 

=GetLastRow("A", "C")

But I got 2 problems with this. Although it successfully gets the content of the last cell for column "C":

  • The formula does not get the one in ws "A", but rather the one in the current ws (i.e. "Z").
  • Whenever I add a new entry in ws "A", the cell in ws "Z" does not get updated at all (even when forcing a recalc).

The only way I could update the cell, is to type Enter on it! 8v|

Also I tried hard-coding the worksheet name in the formula directly but it still fetches the last cell in the current ws... 8v(

Am I doing something wrong? Or maybe I should proceed another way?

I would appreciate if you could help me getting this use-case to work. Thanks.

Maitre Bart
  • 157
  • 1
  • 11
  • 2
    First, I thing you don't need a macro to do this? Have you tried [*OFFSET Function*](http://office.microsoft.com/en-us/excel-help/offset-function-HP010342739.aspx)? As for `Am I doing something wrong?`, you probably need to reference your object correctly and explicitly work on it. In your current code, you are actually getting the last row of the currently selected Sheet since you are not explicit in using `Cells`. – L42 Sep 24 '14 at 02:19
  • Thanks, I'm gonna look for OFFSET. But in the meantime, I can see I am using Cells on the last line. Moreover, its parameter is MyRange and this one comes from the specified ws name passed in argument. So I'm not sure to understand why you say that I am not explicitly using Cells? I would appreciate if you could show me how I can use Cells more explicitly? (I'm not familiar with the Excel model) – Maitre Bart Sep 24 '14 at 02:56
  • `MyRange.Column` because you are only telling it to get the Column from MyRange which is why it keeps getting the value from your current sheet. – Milanor Sep 24 '14 at 03:19
  • Oh, I see. Beginning to understand. – Maitre Bart Sep 24 '14 at 03:27

4 Answers4

2

As L42 pointed out before, the problem is Cells(65536, MyRange.Column).End(xlUp).Value you are referencing it at cells 65536 and the "C Column" from "MyRange"

This is the working version of your function:

Function GetLastRow(strSheet, strColum) As String

Dim MyRange As Range

GetLastRow = Worksheets(strSheet).Range(strColum & "1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp)

End Function

But to be honest, i prefer using this formula INDEX(A!$C:$C,COUNT(A!$C:$C)) just need to adjust the range a bit. Using a macro is a bit overkill, unless you have blank rows in your C Column then i don't find any need of using macro.

Milanor
  • 257
  • 1
  • 2
  • 10
  • Thanks I'm gonna try the formula. I gave a try to something like the INDEX formula you propose. I'm gonna try again (I think I know what I missed). – Maitre Bart Sep 24 '14 at 03:16
  • The INDEX formula as is does not work. The COUNT gives 24 but the column has at least 28 valid values, and does not start at row 0. I really don't understand why COUNT returns 24... I really need to find the last value and using a function is apparently the only way I can get there. – Maitre Bart Sep 24 '14 at 15:05
  • Your formula meets the first part of the use-case: I'm able to get the value in the last column of ws "A". But when I add a new line in ws "A', the cell in ws "Z" is not automatically updated. Even F9 does not update it. I need to edit (F2) and type Enter in order it updates. – Maitre Bart Sep 24 '14 at 15:23
  • well COUNT function only return cell with "number" format with it. If you want to count everything including text you can use COUNTA, and if the cell was not started from first row you just need to modify the range to something like (assume it started from row 5 and end at 1000 or anywhere exceeds the original data row) `INDEX(A!$C$5:$C$1000,COUNTA(A!$C$5:$C%1000))` And do take note that this is for the condition where there are no empty rows between datas in column C. – Milanor Sep 25 '14 at 03:58
2

I found how to make the function update automatically (second part of use-case): I must add

Application.Volatile

in the function's body. So here's the version I'm using now:

Function GetLastRowValue(strSheet, strColum) As String

   Application.Volatile

   Dim MyRange As Range

   GetLastRowValue = Worksheets(strSheet).Range(strColum & "1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp)

End Function

Thanks to all that helped.

Maitre Bart
  • 157
  • 1
  • 11
1

Milanor already answered it but let me re-write your Function explicitly using Cells.

Function GetLastRow(strSheet, strColum) As String
    With Sheets(strSheet)
        GetLastRow = .Cells(.Rows.Count, strcolum).End(xlUp).Row
    End With
End Function

Also have a go on this one which is a good read. HTH

Community
  • 1
  • 1
L42
  • 19,427
  • 11
  • 44
  • 68
  • Thanks. I'm gonna try it. – Maitre Bart Sep 24 '14 at 03:09
  • Your formula returns the row number (the use-case wants the value in it!). I guess the name of the function caused the confusion. But as for the above answer, the cell does not automatically update if I add a new row in ws "A". (see second point of use-case) – Maitre Bart Sep 24 '14 at 15:32
0

I was looking for something similar and this worked for me:

  1. Build the function for getting last row with values in column:
Function LastRow(ws As String, rng As String)

        On Error Resume Next

         LastRow = Sheets(ws).Range(rng).Find(What:="*", _
                                After:=Sheets(ws).Range(rng).Cells(1), _
                                Lookat:=xlPart, _
                                LookIn:=xlFormulas, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False).Value

        On Error GoTo 0

End Function
  1. Enter routine in worksheet "Z", so the function is updated each time you activate the sheet:
 Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Application.CalculateFullRebuild

End Sub
  1. Enter the formula y cell of sheet Z = LastRow("A","C:C")