Results 1 to 8 of 8

Thread: AutoHiding

  1. #1
    JBeaucaire
    Guest

    AutoHiding

    Here's an easy one for you brainiacs.

    I'd like to add a button to an excel sheet that "cleans up" a form that is a LONG list of available items to buy. After the order is completed, I'd like to have that button "hide" all the rows that contain items that weren't ordered.

    I envision a macro that searches column A for the word "hide", if it finds it, that row is hidden and the search continues. When the search finds no more "hide" references in column A, the function ends.

    The reason I don't want to just search the QTY column for values greater than ZERO are that there are some optional items that I think should always be visible. In column A I have it watching the QTY column for that row and showing "hide" if the QTY is zero.

    The rows that I want to always be visible do not have that "hide" formula active in row A. So that row will always be visible.

    So, a short macro or VB code I can apply to a button is what I'd like to use.

    I can make the code:

    Selection.Find(What:="hide", After:=ActiveCell, LookIn:=xlValues, LookAt _
    :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _
    :=True).Activate
    Selection.EntireRow.Hidden = True

    Cells.FindNext(After:=ActiveCell).Activate
    Selection.EntireRow.Hidden = True
    Cells.FindNext(After:=ActiveCell).Activate
    Selection.EntireRow.Hidden = True

    ..etc. But I need to stop the loop at a certain point, right?

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoHiding

    Does ActiveSheet.UsedRange.Rows.Count help you any?

    Brooke

  3. #3
    JBeaucaire
    Guest

    Re: AutoHiding

    Now I've got the code like this:

    Sub AutoHide()
    '
    ' AutoHide Macro
    ' Keyboard Shortcut: Ctrl+Shift+E
    '
    5 Cells.Find(What:="hide", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False).Activate
    Selection.EntireRow.Hidden = True
    10 If Cells.FindNext(After:=ActiveCell).Activate = True Then
    GoTo 20
    20 Selection.EntireRow.Hidden = True
    21 GoTo 10
    22 Else
    30 End If
    End Sub

    the only problem is that it errors out and requires I "debug" and RESET to get out of the error when it runs out of "hide" items to find. I need to trap that error message and cause the loop to exit.

    Jerry

  4. #4
    JBeaucaire
    Guest

    Re: AutoHiding

    Hehe, nevermind. I figured out another way.

    Sub AutoHide()
    '
    ' AutoHide Macro
    ' Macro recorded 4/13/2001 by Jerry Beaucaire
    '
    ' Keyboard Shortcut: Ctrl+Shift+E
    '
    Range("A:A").Select
    Selection.SpecialCells(xlCellTypeFormulas, 2).Select
    Selection.EntireRow.Hidden = True
    End Sub

    Thanks anyway!

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: AutoHiding

    Assuming all the cells in Column have a formula to determine whether to show or hide, The following code might suit. It selects Column A and the finds all cells with a formula, and loops through them, hiding if the column value is "Hide".

    Sub HideRows()
    Dim HideRows As Range
    Dim CurCell As String
    Application.ScreenUpdating = False
    CurCell = ActiveCell.Address
    Range("A:A").Select
    Set HideRows = Selection.SpecialCells(xlFormulas)
    For Each cell In HideRows
    If cell.Value = "Hide" Then
    cell.EntireRow.Hidden = True
    Else
    cell.EntireRow.Hidden = False
    End If
    Next cell
    Range(CurCell).Select
    Application.ScreenUpdating = True
    End Sub

    The macro will fail if it cannot find a formula or if the sheet is protected. Both contingencies could be addressed if required.

    Andrew C

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoHiding

    Why not specify the range explicitly somehow, rather than filtering the entire A:A column by cells with formulas? For example, the code below is much simpler ("A1:A5000" is the portion of column A that intersects with the list). I think (but not sure) that the overhead is less when you don't force Excel to concsider the entire column. Also, this code won't fail if there are no formulas. Setting ScreenUpdating = False also helps in the example below:

    Dim CheckMe As Range

    For Each CheckMe In Range("A1:A5000").Cells

    If CheckMe.Value = "Hide" Then CheckMe.EntireRow.Hidden = True

    Next CheckMe

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: AutoHiding

    Most efficient of all would be an Autofilter - why bother with any code. Unless I know that a range is permantently fixed, i would always rather calculate the extent of the range. Ranges have a habit of changing in my experience, and I would rather not have to recode to cater for such changes. Anyway, why evaluate 5000 rows if only 1000 are relevant.

    Andrew C

  8. #8
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoHiding

    Depends on your definition of "efficient". If the sheet was for my own use, AutoFilter would be the most efficient. If the sheet is to be used by various people of various Excel comfort levels, then writing a good bit of code is more efficient.

    Yes, of course my assumption about knowing the explicit range address is naive. In cases like the once described, my prefered approach would be to write code that figures out where the last cell in the range is, then operates on that range and no more. My example of 5000 rows was not intended to be "so huge" that the whole list is sure to be covered; my intention was, if the list is 5000 items, operate on 5000, not on 65536 (as in the case of A:A).

Posting Permissions

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