none
Speed up VBA formula RRS feed

  • Question

  • Hi,

    I have a VBA formula that takes over 57 seconds to finish. Is it possible to get it to finish in under 10 seconds? If not I have to find a new solution to automatically remove rows thats empty.

    This is the formula:

    Private Sub Worksheet_Activate()

    Application.Calculation = xlCalculationManual

    Application.ScreenUpdating = False

    Application.DisplayStatusBar = False

    Application.EnableEvents = False

        For Each cell In Range("B11:B151")

            If Application.WorksheetFunction.IsNumber(cell) Then

                cell.EntireRow.Hidden = True

            Else

                cell.EntireRow.Hidden = False

            End If

        Next cell

    Application.Calculation = xlCalculationAutomatic

    Application.ScreenUpdating = True

    Application.DisplayStatusBar = True

    Application.EnableEvents = True

    End Sub

    Without the "calculation", "ScreenUpdating", "DisplayStatusBar" and "EnableEvents" it finishes in around 1 minute and 10 seconds. 

    Friday, March 23, 2018 8:01 AM

All replies

  • Hi Jo Andre,

    I modified your code a little.
    It takes one second or less to complete its process.
    Please watch cell C2-C3 (start time - stop time)
        

    [Here's modified code]
    Private Sub Worksheet_Activate()
        Dim timeStart, timeStop As Date ' --- optional
        ' ---
        timeStart = DateTime.Now
        Application.Calculation = xlCalculationManual
        Application.ScreenUpdating = False
        Application.DisplayStatusBar = False
        Application.EnableEvents = False
        ' --- please check the difference between your code and this ------
        Dim myRow As Integer
        For myRow = 11 To 151   ' -- In Range("B11:B151")
            If Application.WorksheetFunction.IsNumber(Cells(myRow, 2).Value) Then
                Rows(myRow).Hidden = True
            Else
                Rows(myRow).Hidden = False
            End If
        Next
        ' ----------------------------------------------------
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
        Application.DisplayStatusBar = True
        Application.EnableEvents = True
        ' --- optional
        timeStop = DateTime.Now
        Range("C1").Value = timeStart
        Range("C2").Value = timeStop
    End Sub
    Regards,

    Ashidacchi >> http://hokusosha.com/

    Friday, March 23, 2018 9:33 AM
  • Hi,

    How is the issue? Has it been resolved?

    If not, please explain what you want to do with your code. Which do you want to do, remove/delete rows or hide/invisible rows?

    And have you tried my code. It takes one second in my laptop.

    Regards,


    Ashidacchi -- http://hokusosha.com/

    Saturday, March 24, 2018 9:27 PM
  • Hi Jo Andre,

    I try to make a test with your code and find that code executing in no time.

    On my side, It is not taking a very long time as you had mentioned above.

    I suggest you to inform us which Excel version you are using?

    Is your workbook has any other code?

    what kind of data you had stored in Range("B11:B151")?

    If possible try to provide us a sample Workbook.

    We will again make a test with it to check the result.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 26, 2018 6:16 AM
    Moderator
  • Hi Ashidacchi,

    Thanks for trying to help! Sorry for the late reply.

    I haven’t tried this formula to an empty sheet, but if it takes you one second, it has to be the other formulas that’s messing with the VBA formulas.

    I tried to translate as much as I could in the excel file. I don’t think the formulas will work for you because they are in Norwegian, but you will get an idea of how it looks and our problem. I had to remove all our prices and some pictures. You can find the file here: https://we.tl/Wstvuna4Tg

    The main goal is to calculate the price for the job on the first sheet called “Kalkyle”, and when we are done calculating we want it to be as easy as possible to send the price offer. I therefore made a new sheet in the excel file called “Tilbudsbrev”. When the calculation is done we want “Tilbudsbrev” (price offer) to show the rows in the calculation that’s been activated (that have text in them). As you see if you make any changes in “Kalkyle”, it takes way to long for excel to show everything in “Tilbudsbrev”. Please note that you have to press the “+” signs to show all the rows. This is so we easier can have control over what’s done and what’s not done.

    There is a update I would like to make, but I don’t know how. I want to be able to add/delete rows in “Kalkyle”, and they will then be added or deleted in “Tilbudsbrev”. If we for example need one more row for a new title of work, or we need more rows under a title. I have no idea if there are a solution to this or if it’s not possible. It would be much appreciated if you have time to look in to this.

    Thanks for all the help an patience!

    Friday, April 13, 2018 7:03 AM
  • Hi,

    Thanks for trying to help! Sorry for the late reply.

    Please see the answer i sent to Ashidacchi. In the answer i provided a download link to the file. 

    Looking forward to an answer! 

    Friday, April 13, 2018 7:05 AM
  • Btw, im on a Macbook pro 3,1 GHz, i5, 2018 mod. We are using Office 365 and excel is updated. 
    Friday, April 13, 2018 7:08 AM
  • Hi Jo Andre,

    If your issue is only about taking time while it opens, please download and check it (which I've shared ).  https://1drv.ms/x/s!AhzOJeY5F3-fj_N1FZVS7iQPD1ILeg

    What I did on this book is:
    (1) VBA code is in sheet "Tilbudsbrev", and it is executed whenever the sheet is active. I suppose it is not good as for performance, and I move that code to ThisWorkbook (Workbook_Open).

    (2) I've found duplicate range(s) with different name ("hide" and "hidethis"), and "hidethis" is used in your VBA code. So, I remove name "hide".

    Regards,

    Ashidacchi -- http://hokusosha.com/

    • Edited by Ashidacchi Friday, April 13, 2018 7:31 AM
    Friday, April 13, 2018 7:30 AM