Find Last Row of a Worksheet that has variable rows

[kengooch]

So I have an equation that counts the number of Yes, No in a workbook, but the workbook number of rows changes each day. Here is the equation

="MWV has "&COUNTA(A3:A307)&" positive COVID Cases."&" Of those cases "&TEXT((COUNTIF(J3:J281,"*Y*")/COUNTA(A3:A307))*100,"###.#")&"% or ("&(COUNTIF(J3:J281,"*Y*")&" cases) were vaccinated and we have ("&COUNTIF(J3:J281,"*n*")&" cases) or "&TEXT((COUNTIF(J3:J281,"*n*")/COUNTA(A3:A307))*100,"###.#")&"% from non-vaccinated staff.")

I can figure out the last row using LOOKUP(2,1 / (A:AB <> ""),ROW(A:A))

So the question is... How do I incorporate the Last Row lookup code into the Count formula. I have tried using " & in many various combinations with no luck. What I need to do is CountA(A3:A"&LOOKUP(2,1 / (A:AB <> ""),ROW(A:A))&")" or something like that is my guess...

 

[arnelgp]

add a Function that will calculate the "column"'s lastrow using VBA.
there are lots of sample in the net.

 

[kengooch]

add a Function that will calculate the "column"'s lastrow using VBA.
there are lots of sample in the net.

So... i would assign that row value to a variable... then can that variable be referenced in the equation? Or should I just try to do the whole calculation in vba and then paste the answer into the spreadsheet?

 

[Gasman]

I always calc the lastrow, then use that in equations or code.

 

[Isaac]

Function lngLastRowInThisSheet() As Long
Application.Volatile
lngLastRowInThisSheet = Range("a" & Rows.Count).End(xlUp).Row
End Function

On a worksheet:

=lngLastRowInThisSheet()

note - make sure that if you use the letter a as my code does, make sure it's a true statement that for every row of data, col A is always filled in. If it's not, adjust accordingly.

note - I'm a bit unsure as to the consequences of using volatile UDF's in Excel, I try to keep vba in vba, and worksheet in worksheets. So what I'm recommending isn't something I've done much of (FYI). May want to read up to get others' advice too if implementing my route. What I do remember hearing over the years is that there are considerations to be had when leveraging volatility ...

 

[kengooch]

I always calc the lastrow, then use that in equations or code.

I can do that in another cell, say, cell H1, so that it now contains the value, but then how do I incorporate that "H1" cell reference into the equation
="MWV has "&COUNTA(A3:A307)&" positive COVID Cases."&" Of those cases "&TEXT((COUNTIF(J3:J281,"*Y*")/COUNTA(A3:A307))*100,"###.#")&"% or ("&(COUNTIF(J3:J281,"*Y*")&" cases) were vaccinated and we have ("&COUNTIF(J3:J281,"*n*")&" cases) or "&TEXT((COUNTIF(J3:J281,"*n*")/COUNTA(A3:A307))*100,"###.#")&"% from non-vaccinated staff.")

 

[Isaac]

I can do that in another cell, say, cell H1, so that it now contains the value, but then how do I incorporate that "H1" cell reference into the equation
="MWV has "&COUNTA(A3:A307)&" positive COVID Cases."&" Of those cases "&TEXT((COUNTIF(J3:J281,"*Y*")/COUNTA(A3:A307))*100,"###.#")&"% or ("&(COUNTIF(J3:J281,"*Y*")&" cases) were vaccinated and we have ("&COUNTIF(J3:J281,"*n*")&" cases) or "&TEXT((COUNTIF(J3:J281,"*n*")/COUNTA(A3:A307))*100,"###.#")&"% from non-vaccinated staff.")

Maybe it would be easier to have the VBA function return the range to be counted (adjust the specifics to suit, but you get the gist), as follows:

Function UsedRangeInColumnA() As Range
Application.Volatile
Set UsedRangeInColumnA = Range("A2:A" & Range("a" & Rows.Count).End(xlUp).Row)
End Function

Worksheet:
=COUNTA(UsedRangeInColumnA())

 

[arnelgp]

you may also try this:

Public Function RangeFromCell(ByRef strCell As String, Optional ByRef WS As Worksheet) As Range
    Dim strColumn As String
    Dim lastrow As Long
    'strCell is a Single cell
    If InStr(1, strCell, ":", vbTextCompare) <> 0 Then
        Exit Function
    End If
    If WS Is Nothing Then Set WS = ActiveSheet
    strCell = Replace$(strCell, "$", "")
    strColumn = getColumnLetter(strCell)
    lastrow = fnLastRow(strColumn, WS)
    Set RangeFromCell = WS.Range(strCell & ":" & strColumn & lastrow)
End Function

' note WS is a Worksheet object
' used as late binding
Public Function fnLastRow(ByVal strColumn As String, Optional ByRef WS As Object) As Long
    If WS Is Nothing Then Set WS = ActiveSheet
    fnLastRow = WS.Cells(WS.Rows.Count, ToColNum(strColumn, WS)).End(-4162).Row
End Function

Public Function fnLastColumn(ByVal lngrow As Long, Optional ByRef WS As Object) As String
    If WS Is Nothing Then Set WS = ActiveSheet
    fnLastColumn = ToColLetter(WS.Cells(lngrow, WS.Columns.Count).End(-4131).Column)
End Function

' https://www.extendoffice.com/documents/excel/3765-excel-convert-column-label-to-number.html
Public Function ToColNum&(ByVal ColName As String, Optional ByRef WS As Object)
    If WS Is Nothing Then Set WS = ActiveSheet
    ToColNum = WS.Range(ColName & 1).Column
End Function

' https://www.extendoffice.com/documents/excel/3765-excel-convert-column-label-to-number.html
Public Function ToColLetter$(ByVal ColNumber As Integer, Optional ByRef WS As Object)
    If WS Is Nothing Then Set WS = ActiveSheet
    ToColLetter = Split(WS.Cells(1, ColNumber).Address, "$")(1)
End Function

Public Function getColumnLetter(ByVal strRange As String) As String
Dim i As Integer, j As Integer
Dim sret As String
j = Len(strRange)
Do While IsNumeric(Mid$(strRange, i + 1, 1)) = False
    i = i + 1
    sret = sret & Mid$(strRange, i, 1)
Loop
sret = Replace$(sret, "$", "")
getColumnLetter = sret
End Function

change your formula to:

="MWV has "&COUNTA(RangeFromCell("A3"))&" positive COVID Cases."&"  Of those cases "&TEXT((COUNTIF(RangeFromCell("J3"),"*Y*")/COUNTA(RangeFromCell("A3")))*100,"###.#")&"% or  ("&(COUNTIF(RangeFromCell("J3"),"*Y*")&" cases) were vaccinated and we have ("&COUNTIF(RangeFromCell("J3"),"*n*")&" cases) or "&TEXT((COUNTIF(RangeFromCell("J3"),"*n*")/COUNTA(RangeFromCell("A3"))*100,"###.#")&"% from non-vaccinated staff.")