+ Reply to Thread
Results 1 to 8 of 8

macro too long and takes too long needs simplifying

  1. 10-26-2016, 09:36 AM #1
    margal
    margal is offline
    Registered User
    Join Date
    12-07-2012
    Location
    london england
    MS-Off Ver
    Excel 2010
    Posts
    21

    macro too long and takes too long needs simplifying

    Hi everyone,


    after a little help if at all possible.


    I have simplified the code below, as its so long! it goes for nearly a thousand lines hence taking forever.

    can anyone simplify this for me at all?


    thank you in advance





    Private Sub Worksheet_Activate()
    Application.EnableCancelKey = xlDisabled
    On Error GoTo Whoa

    Application.ScreenUpdating = False


    If Sheets("ZONE 1").Visible = True Then
    ActiveSheet.Unprotect Password:="1111"
    ActiveWorkbook.Unprotect Password:="2222"
    ActiveSheet.Shapes("Picture 2").Visible = True
    ActiveSheet.Shapes("Picture 1").Visible = False
    ActiveSheet.Protect Password:="1111"
    ActiveWorkbook.Protect Password:="2222"

    ElseIf Sheets("ZONE 2").Visible = True Then
    ActiveSheet.Unprotect Password:="1111"
    ActiveWorkbook.Unprotect Password:="2222"
    ActiveSheet.Shapes("Picture 1").Visible = True
    ActiveSheet.Shapes("Picture 2").Visible = False
    ActiveSheet.Protect Password:="1111"
    ActiveWorkbook.Protect Password:="2222"

    Else

    End If



















    If Range("$S$29") <> "" And Range("$I$29") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("29").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("29").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If

    If Range("$S$30") <> "" And Range("$I$30") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("30").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("30").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If

    If Range("$S$31") <> "" And Range("$I$31") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("31").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("31").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If

    If Range("$S$32") <> "" And Range("$I$32") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("32").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("32").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If


    If Range("$S$33") <> "" And Range("$I$33") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("33").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("33").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If



    If Range("$S$34") <> "" And Range("$I$34") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("34").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("34").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If


    If Range("$S$35") <> "" And Range("$I$35") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("35").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("35").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If



    If Range("$S$36") <> "" And Range("$I$36") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("36").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("36").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If


    If Range("$S$37") <> "" And Range("$I$37") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("37").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("37").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If


    If Range("$S$38") <> "" And Range("$I$38") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("38").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("38").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If


    If Range("$S$39") <> "" And Range("$I$39") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("39").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("39").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If



    If Range("$S$40") <> "" And Range("$I$40") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("40").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("40").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If



    If Range("$S$41") <> "" And Range("$I$41") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("41").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("41").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If


    If Range("$S$42") <> "" And Range("$I$42") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("42").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("42").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If

    If Range("$S$43") <> "" And Range("$I$43") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("43").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("43").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If

    If Range("$S$44") <> "" And Range("$I$44") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("44").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("44").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If

    If Range("$S$45") <> "" And Range("$I$45") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("45").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("45").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If
    If Range("$S$46") <> "" And Range("$I$46") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("46").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("46").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If


    If Range("$S$47") <> "" And Range("$I$47") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("47").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("47").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If





    If Range("$S$48") <> "" And Range("$I$48") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("48").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("48").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If


    If Range("$S$49") <> "" And Range("$I$49") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("49").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("49").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If


    If Range("$S$50") <> "" And Range("$I$50") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("50").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("50").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If


    If Range("$S$51") <> "" And Range("$I$51") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("51").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("51").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If



    If Range("$S$52") <> "" And Range("$I$52") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("52").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("52").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If


    If Range("$S$53") <> "" And Range("$I$53") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("53").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("53").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If

    If Range("$S$54") <> "" And Range("$I$54") > 1 Then
    ActiveSheet.Unprotect Password:="1111"
    Rows("54").EntireRow.Hidden = False
    ActiveSheet.Protect Password:="1111"
    Else
    ActiveSheet.Unprotect Password:="1111"
    Rows("54").EntireRow.Hidden = True
    ActiveSheet.Protect Password:="1111"
    End If
    Reply With QuoteRegister To Reply
  2. 10-26-2016, 10:01 AM #2
    Olly
    Olly is offline
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: macro too long and takes too long needs simplifying

    First of all, please use [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags around any code you post.

    Obviously, your code is incomplete, but I'm guessing it just carries on in a similar fashion with more rows?

    Try something like:
    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...
    Reply With QuoteRegister To Reply
  3. 10-26-2016, 10:17 AM #3
    margal
    margal is offline
    Registered User
    Join Date
    12-07-2012
    Location
    london england
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: macro too long and takes too long needs simplifying

    Hi Olly,


    Do appreciate the very quick response mate

    I also see you like a bit of trackdays!


    currently using a k16 zx10 awesome bit of kit!


    anyway back to what we were talking about lol!

    Olly its coming up

    compile error

    invalid or unqualified reference it then highlights below


    .Rows(l).EntireRow.Hidden = IsEmpty(.Cells(l, "S")) Or Not .Cells(l, "I").Value > 1

    (.cells above is what is highlighted?


    any idea on this mate?


    Appreciated



    simon
    Reply With QuoteRegister To Reply
  4. 10-26-2016, 10:32 AM #4
    Olly
    Olly is offline
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: macro too long and takes too long needs simplifying

    Did you paste the code entirely, or did you try to modify it?

    As posted, it work perfectly for me - see attachment for worked example.

    Avatar is from racing days, many years ago now!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 10-26-2016, 10:35 AM #5
    BellyGas
    BellyGas is offline
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    575

    Re: macro too long and takes too long needs simplifying

    <----- '04 ZX-10R (the Mk1 before they detuned 'em to stop noobs from flipping....and without a factory steering damper too).


    Anyway, rather than all that messing about with passwords, why not just protect your worksheets with the 'userinterfaceonly = true' switch? This allows VBA to make changes to locked cells without having to unlock them...
    Reply With QuoteRegister To Reply
  6. 10-26-2016, 03:42 PM #6
    margal
    margal is offline
    Registered User
    Join Date
    12-07-2012
    Location
    london england
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: macro too long and takes too long needs simplifying

    Guys,


    I managed to get a response from the Microsoft forum which worked perfect!

    I'm so pleased that so many people put their own time in to help people learn.

    To everyone out there,

    thank you.


    the code that worked for me is below



    Private Sub Worksheet_Activate()



    Dim l As Long


    cPWord1 = "1111"
    cPWord2 = "2222"


    With Application
    .EnableCancelKey = xlDisabled
    .ScreenUpdating = False
    .EnableEvents = False
    End With


    On Error GoTo Whoa

    If Sheets("ZONE 1").Visible = True Then
    'ActiveSheet.Unprotect Password:="1111"
    'ActiveWorkbook.Unprotect Password:="2222"
    ActiveSheet.Shapes("Picture 2").Visible = True
    ActiveSheet.Shapes("Picture 1").Visible = False
    'ActiveSheet.Protect Password:="1111"
    'ActiveWorkbook.Protect Password:="2222"
    ElseIf Sheets("ZONE 2").Visible = True Then
    'ActiveSheet.Unprotect Password:="1111"
    'ActiveWorkbook.Unprotect Password:="2222"
    ActiveSheet.Shapes("Picture 1").Visible = True
    ActiveSheet.Shapes("Picture 2").Visible = False
    'ActiveSheet.Protect Password:="1111"
    'ActiveWorkbook.Protect Password:="2222"
    Else
    End If






    'For l = 29 To 300
    ' .Rows(l).EntireRow.Hidden = IsEmpty(.Cells(l, "S")) Or Not .Cells(l, "I").Value > 1
    ' Next l
    ' End With




























    ActiveSheet.Unprotect Password:=cPWord1
    ActiveWorkbook.Unprotect Password:=cPWord2






    Cells(29, 1).Resize(313, 1).EntireRow.Hidden = False ' this displays ALL your rows

    nICol = 9 ' this is your "I" column
    nSCol = 19 ' this is your column "S"
    For nRow = 29 To 313
    If IsEmpty(Cells(nRow, nSCol).Value) Or Not Cells(nRow, nICol).Value > 1 Then
    Cells(nRow, 1).EntireRow.Hidden = True ' this hides only the ones that need hiding
    End If
    Next



    ActiveSheet.Protect Password:=cPWord1
    ActiveWorkbook.Protect Password:=cPWord2






    FallThrough:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
    Whoa:
    If Err Then
    Debug.Print "ERROR", Err.Number, Err.Description
    Err.Clear
    End If

    'MsgBox Err.Description
    Resume FallThrough



    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With





    End Sub


    HOPEFULLY THIS CAN HELP SOMEONE ELSE!!!


    AGAIN THAN YOU EVERYONE!!
    Reply With QuoteRegister To Reply
  7. 10-26-2016, 05:09 PM #7
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: macro too long and takes too long needs simplifying


    Code Tags please ‼
    Reply With QuoteRegister To Reply
  8. 10-26-2016, 05:26 PM #8
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,610

    Re: macro too long and takes too long needs simplifying

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Ben Van Johnson
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] I have a Macro that takes a long time to run, how to make it faster
    By dmcgov in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 01-22-2016, 09:29 AM
  2. [SOLVED] Adding Checkboxes, But Macro still takes too long to execute
    By Bob1980 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-21-2014, 01:17 PM
  3. Inefficient code - macro takes too long
    By dantray02 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-07-2014, 09:29 AM
  4. Macro takes a long time to run, can this process run quicker?
    By club rat in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-13-2014, 02:26 PM
  5. how do i add time to see how long my macro takes to run
    By ernestgoh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-16-2006, 06:10 AM
  6. Time Calculation - How long a macro takes to run
    By cdb in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-31-2005, 04:06 AM
  7. [SOLVED] Counting how long a Macro takes to run
    By Diane Alsing in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-07-2005, 02: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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1