+ Reply to Thread
Results 1 to 7 of 7

Excel macros and Advanced Filtering Issues after converting to Office 365 and Excel 2016

  1. #1
    Registered User
    Join Date
    12-01-2017
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    63

    Excel macros and Advanced Filtering Issues after converting to Office 365 and Excel 2016

    I have created some macro files and advanced filtering tools internally in my company using Excel 2103. Now that the company has converted users over to 365, many macros take excessive times to run or don't run correct and have incomplete results. Advacned filtering files have severe latency or dont ever return results. All of these worked without issues in Excel 2013. Are there any known fixes,chngges that must occur in the original files or compatability fixes?
    Thanks

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,885

    Re: Excel macros and Advanced Filtering Issues after converting to Office 365 and Excel 20

    No, VBA hasn't changed in years and nothing has changed from 2013 to Office 365 (with exception of added objects).
    Any code written in 2013 will work in Office 365.

    I assume it was upgrade from 2013 to Office 365 and not a clean install. This has been known to cause issues at times.

    Try these steps:
    1. Run repair on Office installation
    2. Turn off hardware graphics acceleration in Files->Options->Advanced under Display
    3. If above does not fix issue, uninstall and do a clean install of Office 365

    Unless there is very compelling reason to do so (such as frequently working with data that's over 2Gb in size etc), make sure you install 32 bit Office and not 64 bit.

    Additionally, you can turn off all unnecessary animation via Windows Ease of Access (see link below).
    https://www.zachpfeffer.com/single-p...-in-Excel-2016
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    12-01-2017
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    63

    Re: Excel macros and Advanced Filtering Issues after converting to Office 365 and Excel 20

    Thank You I will try this first It was done as an upgrade and not a clean install

  4. #4
    Registered User
    Join Date
    12-01-2017
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    63

    Re: Excel macros and Advanced Filtering Issues after converting to Office 365 and Excel 20

    HI CK76,
    We have still not been able to find a solution to fix the issues after moving into Office 365. I have been trying to focus on one important file that uses advanced filtering and a small amount of code to trigger the filter or clear the sheet The file works as designed in Excel 2013 and on my home system with Excel 2016. In the new work version using 365 and Excel 2016, it takes an excessive amount of time to open if it does at all and I don/t believe it has ever finished the filtering to return results. It shows processing or just clocking.
    To date:
    We tested backward in Excel 2010 and works OK
    We tried running Office Repair with no success
    We tried running the file on a new build with 365 and it won't work. This should match your suggestion for doing a clean install.
    In addition we have macros that take an extensive amount of time to run compared to in Excel 2013
    Large files are slow to open now that has been solved by moving some users to 64 bit.
    Any thoughts?

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,885

    Re: Excel macros and Advanced Filtering Issues after converting to Office 365 and Excel 20

    Try checking file options and ensure following.

    1. Hardware graphics acceleration is turned off.
    2. Multi-Threaded calculation is turned on, use max available core

    Then...
    1. Check Update Channel of Office 365, ensure you are on either Semi-Annual Channel or Monthly Channel, and updated to latest version
    2. Check if you have any macro/add-in with API reference. If there is one, remove and test.

    Additionally, try running Excel in safe mode and see if you can at least open the file.

    Without detail of your code and workbook, this is as much suggestion as I can come up with. Try contacting MS support with detail of your issue.

  6. #6
    Registered User
    Join Date
    12-01-2017
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    63

    Re: Excel macros and Advanced Filtering Issues after converting to Office 365 and Excel 20

    Hi CK76,
    I wanted to let you know what finally worked. We tested the 2013 file in Excel 2016 with WIN 10 and no good, tested on a bran new PC with 365/Excel 2016 and no good. Excel 2013 and 2010 worked fine. The final solution was to save the file in 2013, open it with 365./excel 2016 and run a sheet compare tool. A colleague did this task and he waasnt sure what if anything occured, but the file now runs in 365/2016. I'm not familiar with the sheet compare function myself. Thank you for the help

  7. #7
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,885

    Re: Excel macros and Advanced Filtering Issues after converting to Office 365 and Excel 20

    Thanks for the update Glad you got it solved.

    Though still odd that sheet compare fixed it. I don't think that really changes anything

+ 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. Spread sheet issues with Office 2016
    By Poppi in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-03-2018, 12:59 PM
  2. excel & office 2016 for £10 ???
    By Trebor777 in forum Excel General
    Replies: 2
    Last Post: 10-31-2017, 02:39 PM
  3. Replies: 0
    Last Post: 07-05-2017, 09:03 PM
  4. Replies: 1
    Last Post: 12-18-2016, 11:47 PM
  5. Replies: 1
    Last Post: 08-07-2016, 05:52 PM
  6. Conditional formatting with udf issues with Office 2016 / Office 365
    By andikeep2580 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-22-2016, 10:58 AM
  7. [SOLVED] advanced filtering issues
    By derrickh13 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-15-2013, 03:09 PM

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