+ Reply to Thread
Results 1 to 7 of 7

Simplifying VB code

Hybrid View

odggi Simplifying VB code 09-29-2006, 08:00 AM
Bryan Hessey Count which there are most... 09-29-2006, 08:41 AM
JR@SGC Hi should look like the... 09-29-2006, 08:49 AM
odggi Hi JR, I've ended up with:... 09-29-2006, 10:04 AM
JR@SGC Ah sorry...... I ment it... 09-29-2006, 10:15 AM
Bryan Hessey Application.ScreenUpdating =... 09-30-2006, 07:09 AM
funkymonkUK you could add row and then... 09-29-2006, 08:47 AM
  1. 09-29-2006, 08:00 AM #1
    odggi is offline
    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
    Reply With QuoteRegister To Reply
  2. 09-29-2006, 08:41 AM #2
    Bryan Hessey is offline
    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
    ---
    Reply With QuoteRegister To Reply
  3. 09-29-2006, 08:49 AM #3
    JR@SGC is offline
    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
    Reply With QuoteRegister To Reply
  4. 09-29-2006, 10:04 AM #4
    odggi is offline
    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
    Reply With QuoteRegister To Reply
  5. 09-29-2006, 10:15 AM #5
    JR@SGC is offline
    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,
    Reply With QuoteRegister To Reply
  6. 09-30-2006, 07:09 AM #6
    Bryan Hessey is offline
    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
    Application.ScreenUpdating = False 
Range("A:AZ").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
Application.ScreenUpdating = True
    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 With QuoteRegister To Reply
  7. 09-29-2006, 08:47 AM #7
    funkymonkUK is offline
    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
    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)

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