+ Reply to Thread
Results 1 to 7 of 7

Simplifying VB code

  1. #1
    Registered User
    Join Date
    09-03-2006
    Posts
    8

    Simplifying VB code

    Hello All,

    How do I go about simplifying the VB code below:

    Private Sub sTest()
    Application.ScreenUpdating = False
    Columns("A").EntireColumn.Hidden = False
    Columns("B").EntireColumn.Hidden = True
    Columns("C:F").EntireColumn.Hidden = False
    Columns("G:H").EntireColumn.Hidden = True
    Columns("I:L").EntireColumn.Hidden = False
    Columns("M:O").EntireColumn.Hidden = True
    Columns("P:V").EntireColumn.Hidden = False
    Columns("W").EntireColumn.Hidden = True
    Columns("X").EntireColumn.Hidden = False
    Columns("Y").EntireColumn.Hidden = True
    Columns("Z").EntireColumn.Hidden = False
    Columns("AA").EntireColumn.Hidden = True
    Columns("AB:AF").EntireColumn.Hidden = False
    and on and on and on……….
    End Sub

    Thanking you all in advance
    Odggi

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by odggi
    Hello All,

    How do I go about simplifying the VB code below:

    Private Sub sTest()
    Application.ScreenUpdating = False
    Columns("A").EntireColumn.Hidden = False
    Columns("B").EntireColumn.Hidden = True
    Columns("C:F").EntireColumn.Hidden = False
    Columns("G:H").EntireColumn.Hidden = True
    Columns("I:L").EntireColumn.Hidden = False
    Columns("M:O").EntireColumn.Hidden = True
    Columns("P:V").EntireColumn.Hidden = False
    Columns("W").EntireColumn.Hidden = True
    Columns("X").EntireColumn.Hidden = False
    Columns("Y").EntireColumn.Hidden = True
    Columns("Z").EntireColumn.Hidden = False
    Columns("AA").EntireColumn.Hidden = True
    Columns("AB:AF").EntireColumn.Hidden = False
    and on and on and on……….
    End Sub

    Thanking you all in advance
    Odggi
    Count which there are most of, and do A:AF of that (false) in bulk, then do the others.

    On a sheet you can CTRL+Select other columns, try recording a macro and doing that for the other (true) half.

    hth
    ---

  3. #3
    Forum Contributor funkymonkUK's Avatar
    Join Date
    01-07-2005
    Location
    London, England
    Posts
    500
    you could add row and then put a 0 or a 1 in the cell and depending on whether it is a 0 or 1 will determine whether it should be hidden or not.

    eg.

    Sub test()
    For Each cell In Range("1:1")
    If cell.Value = 1 Then
    cell.EntireColumn.Hidden = True
    Else
    cell.EntireColumn.Hidden = False
    End If
    Next cell
    End Sub

  4. #4
    Forum Contributor
    Join Date
    09-04-2006
    Location
    Yorkshire, England
    Posts
    267
    Hi

    should look like the below

    Range("C:C,E:E,G:G,I:I,K:K,M:M,O:O,Q:Q").Select
    Selection.EntireColumn.Hidden = True
    JR
    Versions
    Mac OS X 'Leopard'
    Mac MS Office Excel 2004
    Windows XP
    MS Excel 2002
    MS Excel 2003

  5. #5
    Registered User
    Join Date
    09-03-2006
    Posts
    8

    Smile

    Hi JR,

    I've ended up with:

    Range("A:A,C:F,I:L,P:V,X:X,Z:Z,AB:AE,AG:AG,AJ:AK,AN:AN....").EntireColumn.Hidden = False
    Range("B:B,G:H,M:O,W:W,Y:Y,AA:AA,AF:AF,AH:AI,AL:AM...").EntireColumn.Hidden = True

    This has reduced about 40 lines of coding to just 2, BRILLIANT.
    You've been a great help, thanks, and thank you all for your contribution.

    Odggi

  6. #6
    Forum Contributor
    Join Date
    09-04-2006
    Location
    Yorkshire, England
    Posts
    267
    Ah sorry......

    I ment it should look something like this as in apperance not content

    My bad,

  7. #7
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by odggi
    Hi JR,

    I've ended up with:

    Range("A:A,C:F,I:L,P:V,X:X,Z:Z,AB:AE,AG:AG,AJ:AK,AN:AN....").EntireColumn.Hidden = False
    Range("B:B,G:H,M:O,W:W,Y:Y,AA:AA,AF:AF,AH:AI,AL:AM...").EntireColumn.Hidden = True

    This has reduced about 40 lines of coding to just 2, BRILLIANT.
    You've been a great help, thanks, and thank you all for your contribution.

    Odggi
    Please Login or Register  to view this content.
    would not disturb the natives so much and you would not need to amend the first setting when things change (except to set it to the most-right column).
    ---
    Last edited by Bryan Hessey; 09-30-2006 at 07:37 AM.

+ Reply to Thread

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