+ Reply to Thread
Results 1 to 4 of 4

VBA code runs slow

Hybrid View

jamfz VBA code runs slow 08-24-2016, 09:39 AM
Kenneth Hobson Re: VBA code runs slow 08-24-2016, 10:08 AM
jamfz Re: VBA code runs slow 08-24-2016, 11:24 AM
Kenneth Hobson Re: VBA code runs slow 08-24-2016, 12:25 PM
  1. #1
    Registered User
    Join Date
    12-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    88

    VBA code runs slow

    I have a sheet containing this code and some other stuff too. But this section of code causes the macro to run slow. Is there any way to speed this up and make it run faster? Basically when a user enters something into one of the cells it will show the sheet associated with that cell. Hopefully there is a better way.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Set rng = ActiveCell
    
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    
    
    
        If Range("AB193") <> "" Then
            Worksheets("Issue 1").Visible = xlSheetVisible
        Else
            Worksheets("Issue 1").Visible = xlSheetHidden
        End If
        If Range("AB194") <> "" Then
            Worksheets("Issue 2").Visible = xlSheetVisible
        Else
            Worksheets("Issue 2").Visible = xlSheetHidden
        End If
        If Range("AB195") <> "" Then
            Worksheets("Issue 3").Visible = xlSheetVisible
        Else
            Worksheets("Issue 3").Visible = xlSheetHidden
        End If
        If Range("AB196") <> "" Then
            Worksheets("Issue 4").Visible = xlSheetVisible
        Else
            Worksheets("Issue 4").Visible = xlSheetHidden
        End If
        If Range("AB197") <> "" Then
            Worksheets("Issue 5").Visible = xlSheetVisible
        Else
            Worksheets("Issue 5").Visible = xlSheetHidden
        End If
        If Range("AB198") <> "" Then
            Worksheets("Issue 6").Visible = xlSheetVisible
        Else
            Worksheets("Issue 6").Visible = xlSheetHidden
        End If
        If Range("AB199") <> "" Then
            Worksheets("Issue 7").Visible = xlSheetVisible
        Else
            Worksheets("Issue 7").Visible = xlSheetHidden
        End If
        If Range("AB200") <> "" Then
            Worksheets("Issue 8").Visible = xlSheetVisible
        Else
            Worksheets("Issue 8").Visible = xlSheetHidden
        End If
        If Range("AB201") <> "" Then
            Worksheets("Issue 9").Visible = xlSheetVisible
        Else
            Worksheets("Issue 9").Visible = xlSheetHidden
        End If
        If Range("AB202") <> "" Then
            Worksheets("Issue 10").Visible = xlSheetVisible
        Else
            Worksheets("Issue 10").Visible = xlSheetHidden
        End If
        If Range("AB203") <> "" Then
            Worksheets("Issue 11").Visible = xlSheetVisible
        Else
            Worksheets("Issue 11").Visible = xlSheetHidden
        End If
        If Range("AB204") <> "" Then
            Worksheets("Issue 12").Visible = xlSheetVisible
        Else
            Worksheets("Issue 12").Visible = xlSheetHidden
        End If
        If Range("AB205") <> "" Then
            Worksheets("Issue 13").Visible = xlSheetVisible
        Else
            Worksheets("Issue 13").Visible = xlSheetHidden
        End If
        If Range("AB206") <> "" Then
            Worksheets("Issue 14").Visible = xlSheetVisible
        Else
            Worksheets("Issue 14").Visible = xlSheetHidden
        End If
        If Range("AB207") <> "" Then
            Worksheets("Issue 15").Visible = xlSheetVisible
        Else
            Worksheets("Issue 15").Visible = xlSheetHidden
        End If
        If Range("AB208") <> "" Then
            Worksheets("Issue 16").Visible = xlSheetVisible
        Else
            Worksheets("Issue 16").Visible = xlSheetHidden
        End If
         If Range("AB209") <> "" Then
            Worksheets("Issue 17").Visible = xlSheetVisible
        Else
            Worksheets("Issue 17").Visible = xlSheetHidden
        End If
        If Range("AB210") <> "" Then
            Worksheets("Issue 18").Visible = xlSheetVisible
        Else
            Worksheets("Issue 18").Visible = xlSheetHidden
        End If
        If Range("AB211") <> "" Then
            Worksheets("Issue 19").Visible = xlSheetVisible
        Else
            Worksheets("Issue 19").Visible = xlSheetHidden
        End If
        If Range("AB212") <> "" Then
            Worksheets("Issue 20").Visible = xlSheetVisible
        Else
            Worksheets("Issue 20").Visible = xlSheetHidden
        End If
        If Range("AB213") <> "" Then
            Worksheets("Issue 21").Visible = xlSheetVisible
        Else
            Worksheets("Issue 21").Visible = xlSheetHidden
        End If
     
       
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
    End Sub

  2. #2
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: VBA code runs slow

    So you want the macro to execute all of that when any cell in the sheet is changed? Most would just fire it for one cell or a range of cells. One uses Intersect() to limit execution in that case.

    If that above is true, I suspect that you want to just reset sheets for that one cell changed. You can code it to each cell if in the Intersect() range.

    To use the approach that I detailed, one would probably just want to execute a specific Case for one cell changed or Default (Case Else) to something else if that cell was in a range but not a specific Case. e.g.
    Sub ken()
      Dim Target As Range
      Set Target = Range("A1")
      
      Select Case True
        Case ActiveCell.Address = Target.Address And Target.Value = ""
          MsgBox ActiveCell.Address & " is empty."
        Case Else
          MsgBox ActiveCell.Address & " is not empty or activecell is not A1."
      End Select
    End Sub
    Of course you can have as many "Case"s as you like.

  3. #3
    Registered User
    Join Date
    12-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    88

    Re: VBA code runs slow

    Thanks for the reply Kenneth. I am not a VBA pro by any means, I'm still learning. Your comment reminded me of an important part I omitted from original posts code. I omitted parts to try and keep it clean and not take focus away from the problem area. The part that hides and unhides sheets is controlled by an intersect. If the users enter their initials in a cell within the range it enters the date in the column next to it, and also unhides the issue sheet that goes with it.
    There is also another single cell that needs to run the macro every time the value changes. Cell B4 - the user enters the number of rows they need for data and the macro unhides only the rows needed.
    The problem is every time anything is entered there is about a 3 second pause before the macro finishes and you are able to enter next cell value.
    I don't need to run everything when any cell is changed but at the time I started it I didn't know any better. It worked fine until I added all the sheets to unhide.


    Private Sub Worksheet_Change(ByVal Target As Range)
    Set rng = ActiveCell
    
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    
    If Target.Cells.Count > 1 Then Exit Sub
    
            If Not Intersect(Target, Range("AD193:AD213")) Is Nothing Then
                With Target(1, -1)
                    .Value = Now
                End With 
    
    'this part hides and unhides rows on the sheet as needed by users for data entry. Rows 8-186
    If Target.Address = "$B$4" Then
        Toggle_Rows
        End If
    
    If Range("AB193") <> "" Then
            Worksheets("Issue 1").Visible = xlSheetVisible
        Else
            Worksheets("Issue 1").Visible = xlSheetHidden
        End If
        If Range("AB194") <> "" Then
            Worksheets("Issue 2").Visible = xlSheetVisible
        Else
            Worksheets("Issue 2").Visible = xlSheetHidden
        End If
        If Range("AB195") <> "" Then
            Worksheets("Issue 3").Visible = xlSheetVisible
        Else
            Worksheets("Issue 3").Visible = xlSheetHidden
        End If
        If Range("AB196") <> "" Then
            Worksheets("Issue 4").Visible = xlSheetVisible
        Else
            Worksheets("Issue 4").Visible = xlSheetHidden
        End If
        If Range("AB197") <> "" Then
            Worksheets("Issue 5").Visible = xlSheetVisible
        Else
            Worksheets("Issue 5").Visible = xlSheetHidden
        End If
        If Range("AB198") <> "" Then
            Worksheets("Issue 6").Visible = xlSheetVisible
        Else
            Worksheets("Issue 6").Visible = xlSheetHidden
        End If
        If Range("AB199") <> "" Then
            Worksheets("Issue 7").Visible = xlSheetVisible
        Else
            Worksheets("Issue 7").Visible = xlSheetHidden
        End If
        If Range("AB200") <> "" Then
            Worksheets("Issue 8").Visible = xlSheetVisible
        Else
            Worksheets("Issue 8").Visible = xlSheetHidden
        End If
        If Range("AB201") <> "" Then
            Worksheets("Issue 9").Visible = xlSheetVisible
        Else
            Worksheets("Issue 9").Visible = xlSheetHidden
        End If
        If Range("AB202") <> "" Then
            Worksheets("Issue 10").Visible = xlSheetVisible
        Else
            Worksheets("Issue 10").Visible = xlSheetHidden
        End If
        If Range("AB203") <> "" Then
            Worksheets("Issue 11").Visible = xlSheetVisible
        Else
            Worksheets("Issue 11").Visible = xlSheetHidden
        End If
        If Range("AB204") <> "" Then
            Worksheets("Issue 12").Visible = xlSheetVisible
        Else
            Worksheets("Issue 12").Visible = xlSheetHidden
        End If
        If Range("AB205") <> "" Then
            Worksheets("Issue 13").Visible = xlSheetVisible
        Else
            Worksheets("Issue 13").Visible = xlSheetHidden
        End If
        If Range("AB206") <> "" Then
            Worksheets("Issue 14").Visible = xlSheetVisible
        Else
            Worksheets("Issue 14").Visible = xlSheetHidden
        End If
        If Range("AB207") <> "" Then
            Worksheets("Issue 15").Visible = xlSheetVisible
        Else
            Worksheets("Issue 15").Visible = xlSheetHidden
        End If
        If Range("AB208") <> "" Then
            Worksheets("Issue 16").Visible = xlSheetVisible
        Else
            Worksheets("Issue 16").Visible = xlSheetHidden
        End If
         If Range("AB209") <> "" Then
            Worksheets("Issue 17").Visible = xlSheetVisible
        Else
            Worksheets("Issue 17").Visible = xlSheetHidden
        End If
        If Range("AB210") <> "" Then
            Worksheets("Issue 18").Visible = xlSheetVisible
        Else
            Worksheets("Issue 18").Visible = xlSheetHidden
        End If
        If Range("AB211") <> "" Then
            Worksheets("Issue 19").Visible = xlSheetVisible
        Else
            Worksheets("Issue 19").Visible = xlSheetHidden
        End If
        If Range("AB212") <> "" Then
            Worksheets("Issue 20").Visible = xlSheetVisible
        Else
            Worksheets("Issue 20").Visible = xlSheetHidden
        End If
        If Range("AB213") <> "" Then
            Worksheets("Issue 21").Visible = xlSheetVisible
        Else
            Worksheets("Issue 21").Visible = xlSheetHidden
        End If
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
    End Sub
    Last edited by jamfz; 08-24-2016 at 11:28 AM.

  4. #4
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: VBA code runs slow

    After your Toggle_Rows End If, you may want to try adding Application.EnableEvents=False. I like to set Application options before ON ERROR GOTO ENDNOW. I restore the options after EndNow:.

    You could check the visible property value before setting. Multiple IF()s should make one consider using Select Case. Sometimes IF()s are required though.

    Multiple IF()s can sometimes be reduced if one sees a pattern. In your case, one IF() is probably sufficient. The key is to use string concatenation. e.g.
    Sub Ken()
      Dim Target As Range
    
      On Error Goto EndNow
    
      Set Target = Range("AD193")
      If Range("AB" & Target.Row) <> "" Then
        Worksheets("Issue " & Target.Row - 192).Visible = xlSheetVisible
      Else
        Worksheets("Issue " & Target.Row - 192).Visible = xlSheetHidden
      End If
    
      EndNow:
    End Sub
    Last edited by Kenneth Hobson; 08-24-2016 at 12:30 PM.

+ 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. [SOLVED] VBA code runs slow when other workbooks are open
    By JimDandy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-27-2016, 12:47 AM
  2. 2003 code runs super slow in 2010
    By emanresu65 in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 01-04-2013, 05:25 PM
  3. VBA Code runs too slow
    By pezalmendra in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-31-2012, 07:19 AM
  4. Handling Pivot Tables with macros - code runs slow
    By Pichingualas in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-12-2012, 10:58 AM
  5. Code runs slow until I bring some other app to foreground
    By patatvs in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-02-2011, 11:48 AM
  6. Fibonacci Code Runs too slow
    By MarvinP in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 09-20-2010, 02:14 PM
  7. VBA code runs slow until I push ESC
    By MCCCLXXXV in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-04-2007, 11:28 AM

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