# How to Use a Worksheet Last Row Function in a sheet cell formula

#### rfs0123

##### New Member
I have around a hundred cells that do a sumproduct formula to calculate total footage of various sized pipe listed on a worksheet called 'Detail' (see the formula below. This works in around 90% of the situations because the total rows in the 'Detail' sheet is less than 6000, the fixed value in the formular. Rrecently we noticed that on larger jobs and/or different sort combinations we exceeded the 6000 fixed last row. I setup a function to dynamically calculate the last row of the 'Detail' sheet for each job but can't figure out how to replace the 6000 in the formula with the DetailLR() function. I've tried adding "" & and '' &, even placing the function in a cell and using that reference. I've tried using Value(Function), but everything has failed so far. I know that I can write the formula using VBA each time we load the worksheet, but I though there must be an easier way. Any ideas?

Code:
``=SUMPRODUCT((Detail!\$E\$10:\$E\$6000=Utilities!\$H\$9)*(Detail!\$C\$10:\$C\$6000=Utilities!J8)*(Detail!\$B\$10:\$B\$6000))+SUMPRODUCT((Detail!\$E\$10:\$E\$6000=Utilities!\$H\$9)*(Detail!\$C\$10:\$C\$6000=Utilities!J7)*(Detail!\$B\$10:\$B\$6000))``

Code:
``````Public Function DetailLR() As Long
Application.Volatile (True)
Dim sh As Worksheet
Set sh = ActiveWorkbook.Worksheets("Detail")
On Error Resume Next
DetailLR = sh.Cells.Find(what:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function``````

Thank you

Bob

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### James006

##### Well-known Member
Hi,

You can use the following by Harlan Grove ...

Code:
``````Function Used(r As Range) As Range
'
' Harlan Grove
' Sun 25 Nov 01
'
Dim q As Range
Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
Set Used = Intersect(r, r.Parent.Range(r.Parent.Cells(1, 1), q))
End Function``````

HTH

#### northwolves

##### Well-known Member
Try:
Code:
``=SUMPRODUCT((offset(Detail!\$E\$10,0,0,counta(Detail!e:e)-9,1)=Utilities!\$H\$9)*(offset(Detail!\$c\$10,0,0,counta(Detail!e:e)-9,1)=Utilities!J8)*(offset(Detail!\$b\$10,0,0,counta(Detail!e:e)-9,1)))+SUMPRODUCT((offset(Detail!\$E\$10,0,0,counta(Detail!e:e)-9,1)=Utilities!\$H\$9)*(offset(Detail!\$c\$10,0,0,counta(Detail!e:e)-9,1)=Utilities!J7)*(Detail!\$B\$10:\$B\$6000))``

#### rfs0123

##### New Member
Thank you Harlan. How would I use this in the Cell Formula reference to the Row 6000?

Bob

#### rfs0123

##### New Member
Thank you,

Just inserting this formula into my Worksheet does not produce an error, but does result in a #NA value. I may need to tweak or check it a bit more to see whether this will ultimately work. Thank you for for time.

Bob

#### James006

##### Well-known Member
Hello again,

With the used function, you could test :

Code:
``=SUMPRODUCT(MAX((LEN(USED(A:A))>0)*ROW(USED(A:A))))``

HTH

Replies
7
Views
221
Replies
0
Views
432
Replies
4
Views
2K
Replies
1
Views
321
Replies
8
Views
1K

1,191,584
Messages
5,987,492
Members
440,097
Latest member
Wint

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back