Results 1 to 7 of 7

Using Column Numbers to define Range (VBA)

This is a discussion on Using Column Numbers to define Range (VBA) within the Excel Questions forums, part of the Question Forums category; Hi again, How can I replace Code: .Columns("A:AI").EntireColumn.Hidden = True within this code with numbered columns? Code: Set WBook = ...

  1. #1
    Board Regular NBVC's Avatar
    Join Date
    Aug 2005
    Location
    Ontario
    Posts
    5,827

    Default Using Column Numbers to define Range (VBA)

    Hi again,

    How can I replace

    Code:
    .Columns("A:AI").EntireColumn.Hidden = True
    within this code with numbered columns?

    Code:
    Set WBook = ActiveWorkbook
    
    For Each WSheet In WBook.Worksheets
        With WSheet
           ' autofit column widths
           .Columns("A:AI").EntireColumn.AutoFit
           .Rows("2:" & LastRow & "").RowHeight = 17
        End With
    Next
    Tried this but doesn't work.

    Code:
    .Range(Columns(1), Columns(LastCol)).EntireColumn.Hidden = True
    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Microsoft MVP - Excel



  2. #2
    MrExcel MVP
    Moderator

    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    Blackboys, East Sussex, UK
    Posts
    9,897

    Default

    Code:
    Range(Columns(1), Columns(LastCol)).EntireColumn.Hidden = True
    This should work, works for me at any rate. What is LastCol returning? An integer? What's it doing? Are you getting an error?

    Regards
    Jon
    Regards,
    Jon von der Heyden

    - Posting guidelines, forum rules and terms of use

    - Post your sheet using HTML tables - why?

    - Read the FAQs

    - Always use CODE tags when posting code, e.g. [CODE]insert code here[/CODE]

    - My Excel Blog

  3. #3
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default

    Hi,

    some possibilities
    Code:
    Sub test()
    Dim a As Integer
    Dim z As Integer
    
    a = 3
    z = 5
    
    Columns(a).Resize(, z - a + 1).Hidden = True
    'or
    Range(Cells(1, a), Cells(1, z)).EntireColumn.Hidden = True
    'or
    Range(Columns(a), Columns(z)).Hidden = True
    End Sub
    kind regards,
    Erik

  4. #4
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,664

    Default

    Code:
    Range(Columns(1), Columns(LastCol)).EntireColumn.Hidden = True
    This should work, works for me at any rate. What is LastCol returning? An integer? What's it doing? Are you getting an error?

    Regards
    Jon
    But, my dear Baron, only on the ActiveSheet as written!!
    Richard Schollar

    Using xl2013

  5. #5
    MrExcel MVP
    Moderator

    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    Blackboys, East Sussex, UK
    Posts
    9,897

    Default

    So in other words you need to activate the sheet first?
    Code:
    For Each wsheet In WBook.Worksheets
        wsheet.Activate
    EDIT: BFG, 3838 posts already? Do you actually work for a living?
    Regards,
    Jon von der Heyden

    - Posting guidelines, forum rules and terms of use

    - Post your sheet using HTML tables - why?

    - Read the FAQs

    - Always use CODE tags when posting code, e.g. [CODE]insert code here[/CODE]

    - My Excel Blog

  6. #6
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default

    sorry, NBVC,

    I missed the meaning of your question

    this will work
    Code:
           .Range(.Columns(1), .Columns(LastCol)).EntireColumn.Hidden = True
    check the DOTS !!!

  7. #7
    Board Regular NBVC's Avatar
    Join Date
    Aug 2005
    Location
    Ontario
    Posts
    5,827

    Default

    sorry, NBVC,

    I missed the meaning of your question

    this will work
    Code:
           .Range(.Columns(1), .Columns(LastCol)).EntireColumn.Hidden = True
    check the DOTS !!!
    Those, dang DOTS!!

    Thanks, Erik, very much.
    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Microsoft MVP - Excel



Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com