Results 1 to 3 of 3

Thread: Excel VBA - Hidding a range

  1. 22nd May 2008, 12:00 PM #1
    WhiteD3
    WhiteD3 is offline TopicToaster
    Join Date
    Feb 2007
    Location
    Brisbane
    Posts
    4,497
    Total Downloaded
    0

    Excel VBA - Hidding a range

    Hiya,

    I want to hide a range of rows on a spreadsheet. The user can add and delete rows so the range changes.

    The original code (written by someone else) worked but hid a row at a time, which takes forever to execute when you've got a 1000+ rows.

    However, I can't work out how to hide a range.

    Would appreciate any help.

    Cheers.

    Sub ShowHideAll()
    ActiveSheet.Unprotect
    Firstrow = 3
    lastrow = Range("EndRow").Row - 1

    If ActiveSheet.btnAll.Caption = "Hide All" Then 'Checks the caption on the show/hide button.
    ActiveSheet.Range(Cells(Firstrow, 1), Cells(lastrow, 12)).Hidden = True
    ElseIf ActiveSheet.btnAll.Caption = "Show All" Then
    ActiveSheet.Range(Cells(Firstrow, 1), Cells(lastrow, 12)).Hidden = False
    End If
    Cells(2, 1).Activate
    ActiveSheet.Protect
    End Sub
    Reply With Quote Reply With Quote
  2. 22nd May 2008, 12:42 PM #2
    WhiteD3
    WhiteD3 is offline TopicToaster
    Join Date
    Feb 2007
    Location
    Brisbane
    Posts
    4,497
    Total Downloaded
    0
    Figured it out.....

    Sub ShowHideAll()
    Dim RangeToHide As Range
    ActiveSheet.Unprotect
    Firstrow = 3
    lastrow = Range("EndRow").Row - 1
    Set RangeToHide = ActiveSheet.Range(Cells(Firstrow, 1), Cells(lastrow, 12))
    If ActiveSheet.btnAll.Caption = "Hide All" Then 'Checks the caption on the show/hide button.
    RangeToHide.EntireRow.Hidden = True
    ActiveSheet.btnAll.Caption = "Show All"
    ActiveSheet.btnPending.Caption = "Show Pending"
    ActiveSheet.btnProgress.Caption = "Show In Progress"
    ActiveSheet.btnDLP.Caption = "Show DLP"
    ActiveSheet.btnComplete.Caption = "Show Complete"
    ActiveSheet.btnLost.Caption = "Show Lost"
    ElseIf ActiveSheet.btnAll.Caption = "Show All" Then
    RangeToHide.EntireRow.Hidden = False
    ActiveSheet.btnAll.Caption = "Hide All"
    ActiveSheet.btnPending.Caption = "Hide Pending"
    ActiveSheet.btnProgress.Caption = "Hide In Progress"
    ActiveSheet.btnDLP.Caption = "Hide DLP"
    ActiveSheet.btnComplete.Caption = "Hide Complete"
    ActiveSheet.btnLost.Caption = "Hide Lost"
    End If
    Cells(2, 1).Activate
    ActiveSheet.Protect
    End Sub
    Reply With Quote Reply With Quote
  3. 22nd May 2008, 01:27 PM #3
    Rosco's Avatar
    Rosco
    Rosco is offline Wizard
    Join Date
    Jan 1970
    Location
    Brisbane
    Posts
    1,132
    Total Downloaded
    0
    Reply With Quote Reply With Quote
« changing AULOR email address | anyone use photoshop CS3 ?? »

Bookmarks

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 AULRO.com ONLY!
 Search All the Web!