+ Reply to Thread
Results 1 to 16 of 16

Fixed lastrow in percentage formula

  1. #1
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    140

    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
    Last edited by Roshan.Shakya; 10-17-2019 at 01:53 PM. Reason: for clarity

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,490

    Re: Fixed lastrow in percentage formula

    What is lastrow3 ???
    What is the data layout ... an Excel sample perhaps !!
    Last edited by PCI; 10-17-2019 at 02:24 PM.
    - Battle without fear gives no glory - Just try

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,954

    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.

    Please post a workbook...
    Ben Van Johnson

  4. #4
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    140

    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. #5
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    140

    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.
    Last edited by Roshan.Shakya; 10-17-2019 at 02:40 PM.

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,954

    Re: Fixed lastrow in percentage formula

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

  7. #7
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,490

    Re: Fixed lastrow in percentage formula

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

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,954

    Re: Fixed lastrow in percentage formula

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

  9. #9
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    140

    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
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,490

    Re: Fixed lastrow in percentage formula

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

  11. #11
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,490

    Re: Fixed lastrow in percentage formula

    THe macro seems working
    See your file attached
    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    140

    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. #13
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,954

    Re: Fixed lastrow in percentage formula

    Please Login or Register  to view this content.

  14. #14
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,490

    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.
    Last edited by PCI; 10-17-2019 at 04:40 PM.

  15. #15
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    140

    Re: Fixed lastrow in percentage formula

    New thing to learn today. This is absolutely superb.

    Thanks protonLeah.

  16. #16
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    140

    Re: Fixed lastrow in percentage formula

    Quote Originally Posted by PCI View Post
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. R1C1 lastRow not lastRow when data combined
    By Spyderwoman in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-28-2016, 05:20 PM
  2. [SOLVED] LastRow - for next loops longer than lastrow?
    By flabb in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-07-2016, 12:39 PM
  3. [SOLVED] Problem reducing a (sometimes) negative value by a fixed percentage
    By Dabooka in forum Excel General
    Replies: 3
    Last Post: 07-14-2015, 07:10 AM
  4. Excel 2007 : Calculate percentage with fixed total
    By not_exelerrated in forum Excel General
    Replies: 6
    Last Post: 03-25-2014, 09:44 AM
  5. [SOLVED] I need to calculate a fixed percentage of closed to total orders
    By wurugu in forum Excel General
    Replies: 8
    Last Post: 06-30-2012, 07:23 PM
  6. percentage fixed in time
    By wlln001 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-10-2008, 12:18 PM
  7. how do i enlarge a pie chart by a fixed percentage?
    By ellen s. in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 11-17-2005, 10:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1