# Find Last Row of a Worksheet that has variable rows (1 Viewer)

#### kengooch

##### Member
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

##### ..forever waiting... waiting for jellybean!
add a Function that will calculate the "column"'s lastrow using VBA.
there are lots of sample in the net.

#### kengooch

##### Member
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

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

#### Isaac

##### Lifelong Learner
Code:
``````Function lngLastRowInThisSheet() As Long
Application.Volatile
lngLastRowInThisSheet = Range("a" & Rows.Count).End(xlUp).Row
End Function``````

On a worksheet:

Code:
``=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

##### Member
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

##### Lifelong Learner
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:

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

Worksheet:
=COUNTA(UsedRangeInColumnA())

#### arnelgp

##### ..forever waiting... waiting for jellybean!
you may also try this:

Code:
``````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
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``````

Code:
``="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.")``

Replies
3
Views
683
Replies
20
Views
2,103
Replies
0
Views
1,789
Replies
2
Views
1,593
Replies
12
Views
1,431