Results 1 to 15 of 15
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Selecting Row to Hide based on Criteria (Excel 2000 - Sr1)

    An easy one... User wants to have the ability to look in column F for an asterisk is a given cell. If an asterisk is present, then hide the entire row for the cell where the asterisk is present. (I am setting the number of rows to search through to 2000.)

    What is the proper coding to get this to work? I know the issue is with the Rows("Counter....) but can't figure out how to make it work. The code I am using is as follows:


    Dim counter As Integer
    For counter = 1 To 2000
    If Worksheets("CV LOG_DETAIL").Cells(counter, "F").Value = "*" Then
    Rows("COUNTER:COUNTER").Select
    Selection.EntireRow.Hidden = True
    End If
    Next counter


    Thanks for the help.
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Selecting Row to Hide based on Criteria (Excel 2000 - Sr1)

    Thanks John.

    I didn't see this post when searching for an answer. I think I have what I need.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Selecting Row to Hide based on Criteria (Excel 2000 - Sr1)

    Gary, without directly answering your question, there's a suite of approaches in <!post=this,15420>this<!/post> thread. Can you use autofilter, like this:

    Range.AutoFilter Field:=n, Criteria1:="<>*"
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Row to Hide based on Criteria (Excel 2000 - Sr1)

    Hi Gary,

    In addition to John's solution, you also might try something like:

    Sub HideAst()
    Dim cell As Range
    Application.ScreenUpdating = False
    For Each cell In ActiveSheet.Range("f:f").End(xlUp)
    If cell.Value = "*" Then
    cell.EntireRow.Hidden = True
    Else: cell.EntireRow.Hidden = False
    End If
    Next
    Application.ScreenUpdating = True
    End Sub

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Selecting Row to Hide based on Criteria (Excel 2000 - Sr1)

    Thanks for the help. Exactly what I needed. The code changed as follows works like a charm. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    Dim counter As Integer
    For counter = 1 To 2000
    If Worksheets("CV LOG_DETAIL").Cells(counter, "F").Value = "*" Then
    Rows(counter).EntireRow.Hidden = True
    End If
    Next counter
    Regards,

    Gary
    (It's been a while!)

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Selecting Row to Hide based on Criteria (Excel 2000 - Sr1)

    Mike,

    As I am not an excel programming "guru"

    What is the purpose of setting the application.screenupdating to fall do?

    Also - For each cell in ActiveSheet.Range("f.f").End(xlup)
    -- would this work if the asterisks are not continuous in coulumn F?
    Regards,

    Gary
    (It's been a while!)

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Selecting Row to Hide based on Criteria (Excel 2000 - Sr1)

    Michael's selection is more flexible as to the number of rows, I may have used something like this (untested air code):

    Dim rngCell as Range
    With Worksheets("CV LOG_DETAIL")
    For Each rngCell in Intersect(.UsedRange, .Columns("F"))
    If Instr(rngCell.Value, "*") then rngCell.EntireRow.Hidden = True
    Next rngCell
    End With
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Selecting Row to Hide based on Criteria (Excel 2000 - Sr1)

    Answering on behalf of Michael <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Application.ScreenUpdating = False turns of all the screen updating (flickering) for each step, helps speed code
    Application.ScreenUpdating = True turns on screen updating

    ActiveSheet.Range("f.f").End(xlup) selects Column F down to the last cell by selecting the last cell from the bottom of the worksheet up, so the final range processed includes any blank cells or anything else. It's slightly more efficient than my Intersect(.UsedRange, ...) code because UsedRange can include empty cells.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Row to Hide based on Criteria (Excel 2000 - Sr1)

    Hi Gary,

    After trying it I discovered that interspersed asterisks are not hidden, thus I changed it to For Each cell In ActiveSheet.Range("f1:f2000")

    Also one other thing that slows it down is checking to see if the row does not contain an asterisk in case the user has changed the asterisk and therefore the row should be unhidden.

  10. #10
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Row to Hide based on Criteria (Excel 2000 - Sr1)

    Or some more gobbledygook!

    <pre>Sub HideAst()
    Dim cell As Range
    Application.ScreenUpdating = False
    For Each cell In ActiveSheet.Range(Cells(1, 6), Cells(Range("f:f").End(xlDown).Row, 6))
    If cell.Value = "*" Then
    cell.EntireRow.Hidden = True
    Else: cell.EntireRow.Hidden = False
    End If
    Next
    Application.ScreenUpdating = True
    End Sub</pre>


  11. #11
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Selecting Row to Hide based on Criteria (Excel 2000 - Sr1)

    Thanks to everyone for your help. I am going to try the code you posted as well. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Row to Hide based on Criteria (Excel 2000 - Sr1)

    I would do it like this:

    <pre>Public Sub HideAst()
    Dim lRowMax As Long, I As Long
    lRowMax = Worksheets("CV LOG_DETAIL").UsedRange.Row + Worksheets("CV LOG_DETAIL").UsedRange.Rows.Count - 2
    Application.ScreenUpdating = False
    For I = lRowMax To 0 Step -1
    If Worksheets("CV LOG_DETAIL").Range("F1").Offset(I, 0).Value = "*" Then
    Worksheets("CV LOG_DETAIL").Range("F1").Offset(I, 0).EntireRow.Hidden = True
    End If
    Next I
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

  13. #13
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Selecting Row to Hide based on Criteria (Excel 2000 - Sr1)

    Thanks Legare,

    Now some questions:

    1. lRowMax = Worksheets("CV LOG_DETAIL").UsedRange.Row + Worksheets("CV LOG_DETAIL").UsedRange.Rows.Count - 2
    - How is IRowMax being calculated, are you getting a count of all rows + count of all rows - 2 (This doesn't make sense to me....)
    - What is this actually doing?

    2. If Worksheets..... .Range("F1").Offset(I,0).value
    - What does the offset (I, 0) do?

    Of course, the users that requested this are happy, however, now want something else that appears to be more complicated. I will probably re-post when they tell me what exactly it is they want.....

    Thanks again for the help.
    Regards,

    Gary
    (It's been a while!)

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Row to Hide based on Criteria (Excel 2000 - Sr1)

    I will try to explain.

    1- The UsedRange property returns a range object for the area of the worksheet that contains data. The Worksheets("CV LOG_DETAIL").UsedRange.Row part of that formula returns the row number of the first row in that range. This accounts for any empty rows at the top of the worksheet. The Worksheets("CV LOG_DETAIL").UsedRange.Rows.Count is a count of the number of rows in the range. If you add those two together, you should have the row number of the last used row in the worksheet. The minus 2 is to adjust for the first part of the formula returning a number one higher than the number of empty rows at the top and because the Offset property being zero based and the row number being one based which causes the sum to come up two higher than needed for the Offset property.

    2- The Offset(I,0) offsets the previous range by I rows and 0 columns. Therefore, if I is 5, it would use the value in cell F6, five rows down and zero columns from F1.
    Legare Coleman

  15. #15
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Selecting Row to Hide based on Criteria (Excel 2000 - Sr1)

    Thanks for the explanation. It does make it a little clearer. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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