+ Reply to Thread
Results 1 to 18 of 18

looking for suitable vba to supercede formula that is a slow coach

  1. #1
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    326

    looking for suitable vba to supercede formula that is a slow coach

    Hi

    i'm using this formula to stack non-contiguous column ranges into contiguous ranges, where the named ranges are:

    BR_StudentID represents non-contiguous range with Blanks in Z2:Z
    NBR_StudentID represents intended contiguous No Blanks range in A2:A

    formula in A2:A:
    Please Login or Register  to view this content.
    The problem is Excel takes up quote a long time to calculate the whole sheet with this formula in place, everytime data is updated in Z2:Z, it spins for quite awhile, that i can read the news on my phone. I did some tests to compare times taken with slightly more rows and what i found was the time required to process the sheet using this formula exponentially increases disproportionately with the few additional number of new rows with data. Indeed, the formula is simply not efficient, and i'm wondering if there is a macro code that can accomplish the same task but faster? I'm familiar with formulas but not so much with vba.

    Will appreciate any help that works


    Stewart

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: looking for suitable vba to supercede formula that is a slow coach

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you

  3. #3
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    326

    Re: looking for suitable vba to supercede formula that is a slow coach

    hi Pepe

    sorry drowning in probelms here


    here's a sample of my problem, it's only 20 rows sample so you probably won't feel the slowness. but in my actual workbook, there are about 16 pairs of columns, and rows reach down to 5,000+ at the moment, so this formula function makes calculation very very slow
    hope you can help..

    Stewart
    Attached Files Attached Files
    Last edited by MannStewart; 11-03-2020 at 09:58 PM.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: looking for suitable vba to supercede formula that is a slow coach

    VBA
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    326

    Re: looking for suitable vba to supercede formula that is a slow coach

    fabulous Jindon

    you're the man

    is the SpecialCells(11) fixed if i apply to other column ranges too..?


    Stewart
    Last edited by MannStewart; 11-04-2020 at 02:28 AM.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: looking for suitable vba to supercede formula that is a slow coach

    No, SpecialCells(11), xlLast is referring to the last cell used in within a worksheet, so no need to change it.

  7. #7
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    326

    Re: looking for suitable vba to supercede formula that is a slow coach

    you mean the last cell of the result range A2:A?
    meaning, if the last cell in A2:A is 10,000, i should mod to:

    SpecialCells(10,000), xlLast

    am i correct ?

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: looking for suitable vba to supercede formula that is a slow coach

    No need.
    Specialcells(11) means the last cell that is used of all columns.

    If the last cell in Col.A is 10000, but the one in Col.C is 15000, it refers to 15000.

  9. #9
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    326

    Re: looking for suitable vba to supercede formula that is a slow coach

    so, i still keep using this line per your code as:

    SpecialCells(11), xlLast

    even if i anticipate the max used cell in the non-contiguous range Z2:Z in future will probably expand to 20,000 right ?

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: looking for suitable vba to supercede formula that is a slow coach

    Prepare blank workbook and run then code
    Please Login or Register  to view this content.
    It should give you Y30, intersect of last column/row used.

  11. #11
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    326

    Re: looking for suitable vba to supercede formula that is a slow coach

    yes, on a blank new workbook,
    on run, Msgbox is:

    $Y$30

    so, still use:
    SpecialCells(11), xlLast ?

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: looking for suitable vba to supercede formula that is a slow coach

    It depends how you want it.

    If you write like
    Please Login or Register  to view this content.
    And you only have header in A1, it will clear A1.
    That was what i wanted to avoid.

    But again, it fully depends on you.

  13. #13
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    326

    Re: looking for suitable vba to supercede formula that is a slow coach

    i want to keep Header A1, as well as all other ranges have row1 as header, that needs to be untouched by the macro.

    but i have other non-contiguous ranges, like CZ2:CZ that i want to stack into CA2:CA, and DZ2:DZ stack data into DA2:DA. CZ right now has about 3,000+ rows, and DZ about 4,000+ rows, with blanks in between, so i don't know in future how deep they will expand to, but i just need to write the code now,
    so, i can do like this?:


    Please Login or Register  to view this content.

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: looking for suitable vba to supercede formula that is a slow coach

    Did you actually test it for yourself?
    Any error or not working as you expected?

  15. #15
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    326

    Re: looking for suitable vba to supercede formula that is a slow coach

    yes, i tried & it worked for A2:A and Z2:Z where now Z2:Z last row is 4,000+, all stacked into A2:A nicely, contiguously the no.of rows stacked into A was about 166.

    i just need to do the same for about 15 more pairs of columns

  16. #16
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    326

    Re: looking for suitable vba to supercede formula that is a slow coach

    hi Jindon

    the code is currently running fine under Worksheet.Activate of the sheet the ranges are located.

    i realise i have to get the code executed on workbook open instead of in the sheet, so to apply it to Workbook.Open, without having to activate the sheet the ranges i.e. sheets("sport") are on, how should i adapt the code then?


    Stewart
    Last edited by MannStewart; 11-05-2020 at 12:04 AM.

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: looking for suitable vba to supercede formula that is a slow coach

    I'm out at the moment.
    You can call Activate event code like
    Please Login or Register  to view this content.
    Where Sheet1 is code name of the worsheet.

  18. #18
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    326

    Re: looking for suitable vba to supercede formula that is a slow coach

    hey Jindon


    i need your help with following code, i’m trying to tackle 8 columns at once but the third line

    Please Login or Register  to view this content.
    returns an error: “Runtime error 13, Type mismatch” can you point me out what’s wrong ?
    Please Login or Register  to view this content.
    and, i like to ask if instead of absolutely specifying all the way down to row 10000, it is possible to replace it with up till last filled row of HR like for eg. something like “HR” & Rows.Count).End(xlUp)

    Stewart
    Last edited by MannStewart; 11-14-2020 at 01:18 AM.

+ 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. Vlookup is not working help me with any other formula for this
    By vimalanathk in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-19-2019, 01:53 AM
  2. formula for this?
    By vimalanathk in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-21-2016, 03:20 AM
  3. Filling the cell with the data in second sheet
    By azbi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-20-2015, 01:12 AM
  4. HELP Using the =if formula (or a more suitable one for my needs).
    By tomow87 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-18-2014, 11:51 PM
  5. [SOLVED] What is the suitable formula if getting error: #VALUE!
    By umais41 in forum Excel General
    Replies: 4
    Last Post: 12-31-2012, 03:19 PM
  6. Football Coach needs help
    By aldy67 in forum Excel General
    Replies: 1
    Last Post: 02-08-2006, 02:45 PM
  7. [SOLVED] its coach again......
    By Coach Mike Wyatt in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-21-2005, 12:06 PM

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