# Fixed lastrow in percentage formula

1. ## Fixed lastrow in percentage formula

Respected MVPs, experts and gurus,

I have written the following code to calculate the % out of total for all the rows. I am sure there is a better way to do it.

``Please Login or Register  to view this content.``
I wrote this code as I cannot make the denominator fixed. For 2,000 rows it is not a big deal but some sheets have 100,000 rows which might take significant time to execute this loop. Instead of this loop, copying the formula from third row and pasting it from fourth row to lastrow3 (fixed with "\$" sign) should be the simple way to execute the code quickly.

I have thought the following code but could not fathom "..................." :

``Please Login or Register  to view this content.``
The Total is at lastrow3 plus one cell down.

Thank you
Roshan Shakya

2. ## Re: Fixed lastrow in percentage formula

What is lastrow3 ???
What is the data layout ... an Excel sample perhaps !!

3. ## Re: Fixed lastrow in percentage formula

I don't understand what "...lastrow3 (fixed with "\$" sign)..." means.

You can fetch the last row in column F with:
``Please Login or Register  to view this content.``

4. ## Re: Fixed lastrow in percentage formula

lastrow3 is like any other last row where the data ends. I have assigned different last rows (in the same sheet) as I need them referring in multiple occasions and the different lastrows (lastrow, lastrow1, .....) allows me to work on different part of excel sheet.

lastrow3 plus one cell down has the sum total from start cell to lastrow cell.

Thank you
Roshan Shakya

5. ## Re: Fixed lastrow in percentage formula

Let's assume lastrow3 as 3125th row and the cell having SUM Total is D3126.

If I copy =D3/D3126 from Cell F3 and paste it all the way down through to F3125 then the last cell F3125 will have =D3125/D6249. My requirement is to make the denominator fixed such that F3125 will have D3125/\$D\$3126.

The data are dynamic and lastrow keeps on changing while processing the data and for this purpose I have used lastrow3 (assigned variable for last row) to identify the last row before executing my Loop code. In my Loop code the use of "i" subtracting from the lastrow3 +1 offsets this one cell down movement.

6. ## Re: Fixed lastrow in percentage formula

Please post a workbook. You can/should use *AUTOFILL" vs. looping....

7. ## Re: Fixed lastrow in percentage formula

Perhaps ...!
``Please Login or Register  to view this content.``

8. ## Re: Fixed lastrow in percentage formula

something like:
``Please Login or Register  to view this content.``
But we're still guessing...

9. ## Re: Fixed lastrow in percentage formula

Here you go. I have attached the file.

Column F has the output I desired.

Thank you so much!!!
Roshan Shakya

10. ## Re: Fixed lastrow in percentage formula

Another one more funny
``Please Login or Register  to view this content.``

11. ## Re: Fixed lastrow in percentage formula

THe macro seems working
``Please Login or Register  to view this content.``

12. ## Re: Fixed lastrow in percentage formula

Thanks PCI for the solution. It's a nice code to get a solution. I have a solution using formula For LOOP and my intention is to have the formula in it and not the absolute value.

Can I have something like this in Cell 'F25' "= D25/\$D\$3126"?

For this example, my lastrow3 is row 3125 and it may change. The SUM total formula was written like this:

``Please Login or Register  to view this content.``
Thank you
Roshan Shakya

13. ## Re: Fixed lastrow in percentage formula

``Please Login or Register  to view this content.``

14. ## Re: Fixed lastrow in percentage formula

You were complainng that it takes time to scroll down the formula ... the same if you keep formulas you slowing down Excel ...!
For last row see next code
``Please Login or Register  to view this content.``
Well now you have enough code sample to be happy.

15. ## Re: Fixed lastrow in percentage formula

New thing to learn today. This is absolutely superb.

Thanks protonLeah.

16. ## Re: Fixed lastrow in percentage formula

Originally Posted by PCI
You were complainng that it takes time to scroll down the formula ... the same if you keep formulas you slowing down Excel ...!
For last row see next code
``Please Login or Register  to view this content.``
Well now you have code sample to be happy.
I appreciate your code too. And I am happy to have diverse samples of codes for getting the same result.

Thank you
Roshan Shakya

There are currently 1 users browsing this thread. (0 members and 1 guests)