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

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

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.

Please post a workbook...

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.

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

Perhaps ...!

Please Login or Register  to view this content.

something like:

Please Login or Register  to view this content.

But we're still guessing...

Another one more funny

Please Login or Register  to view this content.

THe macro seems working
See your file attached

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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.