Results 1 to 5 of 5

Simplify VBA Code

Threaded View

  1. #1
    Forum Contributor
    Join Date
    02-14-2011
    Location
    West Valley City, Utah
    MS-Off Ver
    Excel 2010
    Posts
    108

    Simplify VBA Code

    Good afternoon all,

    I've got an insanely long code that is being used to hide/unhide rows based on a data validations field. The formulas and macros are working perfectly as it is, but as I get further into my document (which is a total of 2000 lines that will have a hide/unhide formula attacherd to them), it is beginning to take longer and longer for Excel to update (which it does every time a cell is clicked). Attached is a basic file that demonstrates what I'm doing. Code Example.xlsm

    To simplify my issue, anytime a cell is clicked, it runs the below code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("$C$1") = "Red" Then
    Range("A3:A4").EntireRow.Hidden = False
    Else
    Range("A3:A4").EntireRow.Hidden = True
    End If
    If Range("$C$1") = "Green" Then
    Range("A5:A6").EntireRow.Hidden = False
    Else
    Range("A5:A6").EntireRow.Hidden = True
    End If
    If Range("$C$3") = "Yellow" Then
    Range("A7:A8").EntireRow.Hidden = False
    Else
    Range("A7:A8").EntireRow.Hidden = True
    End If
    If Range("$C$10") = "Chevy" Then
    Range("A12:A13").EntireRow.Hidden = False
    Else
    Range("A12:A13").EntireRow.Hidden = True
    End If
    If Range("$C$10") = "Ford" Then
    Range("A14:A15").EntireRow.Hidden = False
    Else
    Range("A14:A15").EntireRow.Hidden = True
    End If
    If Range("$C$10") = "Toyota" Then
    Range("A16:A17").EntireRow.Hidden = False
    Else
    Range("A16:A17").EntireRow.Hidden = True
    End If
    If Range("$C$19") = "PS2" Then
    Range("A21:A22").EntireRow.Hidden = False
    Else
    Range("A21:A22").EntireRow.Hidden = True
    End If
    If Range("$C$19") = "PS3" Then
    Range("A23:A24").EntireRow.Hidden = False
    Else
    Range("A23:A24").EntireRow.Hidden = True
    End If
    If Range("$C$19") = "Xbox" Then
    Range("A25:A26").EntireRow.Hidden = False
    Else
    Range("A25:A26").EntireRow.Hidden = True
    End If
    End Sub
    and anytime a cell is clicked, it runs the code again to see if anything has changed. As stated above, my issue is that running this formula every time a cell is clicked is beginning to take a lot of time, and I'm currently at line 200 out of 2000.

    Is there any way to simplify the above code and make it more efficient / effective?
    Last edited by Sky188; 09-28-2012 at 02:41 PM.

Thread Information

Users Browsing this Thread

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

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