Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lost in Hiding Rows... (XL 2k SR1)

    If Worksheets("CV LOG_DETAIL").Range("F1").Offset(I, 0).Value <> "*" Then

    Shouldn't this be (I thought Col D for Asterisk?)
    If Worksheets("CV LOG_DETAIL").Range("D1").Offset(I, 0).Value <> "*" Then

    Try this instead on NULL
    If Worksheets("CV LOG_DETAIL").Range("D1").Offset(I, 0).Value <>""

    Could use 1 "IF..Else IF's ... End if" statement instead of all the "IF..End if" statements

    Steve

  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

    Lost in Hiding Rows... (XL 2k SR1)

    I am lost in hiding rows and cant find my way out... Sort of like, Help, I have fallen and cant get up.... Anyway.

    Here is what I am up against. User gave me a spreadsheet (Simple enough) and said develop a macro to hide all rows where Cells in Column D contain as asterisk. Got the answer yesterday, worked great. However, here are the real requirements that were determined when the hiding feature did not work as wanted and we reviewed the worksheet.

    1. If Cells in column F contain an asterisk, then don't hide the rows.
    2. If Cells in column D start with Total, then don't hide the rows.
    3. If Cells in column D start with SubTotal, then don't hide the rows.
    4. If Cells in column D start with Grand, then don't hide the rows.
    5. If Cells in column A start with C. (C dot) then don't hide the rows.
    6. If Cells in Column D are null then don't hide the rows.
    7. Don't hide any rows in rows 1 - 5
    Otherwise, hide the rows.

    I created this really ugly statement to do this (See below) It appears to work OK except for the =null part. Any suggestions on how to make this work or even better how to improve it or make it shorter.....

    I have also attached a copy of the excel workbook for use. Any help is greatly appreciated. <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15>

    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 5 Step -1
    If Worksheets("CV LOG_DETAIL").Range("F1").Offset(I, 0).Value <> "*" Then
    If Left(Worksheets("CV LOG_DETAIL").Range("D1").Offset(I, 0).Value, 5) <> "Total" Then
    If Left(Worksheets("CV LOG_DETAIL").Range("D1").Offset(I, 0).Value, 8) <> "SubTotal" Then
    If Left(Worksheets("CV LOG_DETAIL").Range("D1").Offset(I, 0).Value, 5) <> "Grand" Then
    If Left(Worksheets("CV LOG_DETAIL").Range("A1").Offset(I, 0).Value, 2) <> "C." Then
    If Worksheets("CV LOG_DETAIL").Range("D1").Offset(I, 0).Value = Null Then
    Worksheets("CV LOG_DETAIL").Range("F1").Offset(I, 0).EntireRow.Hidden = False
    Else: Worksheets("CV LOG_DETAIL").Range("F1").Offset(I, 0).EntireRow.Hidden = True
    End If
    End If
    End If
    End If
    End If
    End If
    Next I
    Application.ScreenUpdating = True
    Attached Files Attached Files
    Regards,

    Gary
    (It's been a while!)

  3. #3
    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: Lost in Hiding Rows... (XL 2k SR1)

    Thanks for the input. The asterisk comparison is actually column F. My mistake. The original post has been edited.

    You said to try <<If Worksheets("CV LOG_DETAIL").Range("D1").Offset(I, 0).Value <>"" >>
    I tried this as well as <> " " and it didn't work, but I will try it again.

    Thanks.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lost in Hiding Rows... (XL 2k SR1)

    My Mistake,
    If Worksheets("CV LOG_DETAIL").Range("D1").Offset(I, 0).Value =""
    Steve

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

    Re: Lost in Hiding Rows... (XL 2k SR1)

    Try this:

    <pre>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 5 Step -1
    If I <= 5 Then Exit Sub
    If (Worksheets("CV LOG_DETAIL").Range("F1").Offset(I, 0).Value = "*") Or _
    (Left(Worksheets("CV LOG_DETAIL").Range("D1").Offset(I, 0).Value, 5) = "Total") Or _
    (Left(Worksheets("CV LOG_DETAIL").Range("D1").Offset(I, 0).Value, 8) = "SubTotal") Or _
    (Left(Worksheets("CV LOG_DETAIL").Range("D1").Offset(I, 0).Value, 5) = "Grand") Or _
    (Left(Worksheets("CV LOG_DETAIL").Range("A1").Offset(I, 0).Value, 2) = "C.") Or _
    (Worksheets("CV LOG_DETAIL").Range("D1").Offset(I, 0).Value = "") Then
    Worksheets("CV LOG_DETAIL").Range("F1").Offset(I, 0).EntireRow.Hidden = False
    Else
    Worksheets("CV LOG_DETAIL").Range("F1").Offset(I, 0).EntireRow.Hidden = True
    End If
    Next I
    Application.ScreenUpdating = True
    </pre>

    Legare Coleman

  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: Lost in Hiding Rows... (XL 2k SR1)

    Thanks Legare.

    Works almost perfectly. For whatever reason, the cells containing Subtotal become hidden as well. I have checked the cells for extra spaces, misspelling, even re-typed them and they still disapear. Also checked the cells format and re-set it to text. I don't know why the rows where the value in D = Subtotal becomes hidden as well. Any ideas on as to why?

    ... so thats how to do OR statements in excel....I tried several variations that obviously didn't work.

    ... So close, yet so far... <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    Regards,

    Gary
    (It's been a while!)

  7. #7
    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: Lost in Hiding Rows... (XL 2k SR1)

    ... I got it. I didn't realize that Excel is Case Sensitive. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> If you change SubTotal to Subtotal, the macro works great.

    Thanks again for your help. Couldn't have done it without you. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

  8. #8
    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: Lost in Hiding Rows... (XL 2k SR1)

    One last novice excel question.

    When I give the users their spreadsheet back, where is the macro stored, i.e., Personal, Workbook, etc., so the users can run it?
    Regards,

    Gary
    (It's been a while!)

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

    Re: Lost in Hiding Rows... (XL 2k SR1)

    If this is the only workbook where the macro will be used, then just put it in a module in the workbook.
    Legare Coleman

  10. #10
    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: Lost in Hiding Rows... (XL 2k SR1)

    Thanks again. Works like a charm. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,

    Gary
    (It's been a while!)

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lost in Hiding Rows... (XL 2k SR1)

    If worried about the possibility of Upper/lower case just make everything uppercase before you compare:

    For example:
    (Ucase(Left(Worksheets("CV LOG_DETAIL").Range("D1").Offset(I, 0).Value, 5)) = "TOTAL")

    Steve

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lost in Hiding Rows... (XL 2k SR1)

    Repeating the object reference to the same worksheet over and over again is not the most efficient way.

    This (untested) should improve speed considerably:

    Dim lRowMax As Long, I As Long
    lRowMax = .UsedRange.Row + .UsedRange.Rows.Count - 2
    Application.ScreenUpdating = False
    For I = lRowMax To 5 Step -1
    If I <= 5 Then Exit Sub
    With Worksheets("CV LOG_DETAIL").Range("D1")
    If (.Offset(I, 2).Value = "*") Or _
    (Left(.Offset(I, 0).Value, 5) = "Total") Or _
    (Left(.Offset(I, 0).Value, 8) = "SubTotal") Or _
    (Left(.Offset(I, 0).Value, 5) = "Grand") Or _
    (Left(.Offset(I, -3).Value, 2) = "C.") Or _
    (.Offset(I, 0).Value = "") Then
    .Offset(I, 2).EntireRow.Hidden = False
    Else
    .Offset(I, 2).EntireRow.Hidden = True
    End If
    End With
    Next I
    Application.ScreenUpdating = True
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  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: Lost in Hiding Rows... (XL 2k SR1)

    I tried the code as indicated. Needed to change lRowMax = .UsedRange.Row + .UsedRange.Rows.Count - 2 to

    lRowMax = Worksheets("CV LOG_DETAIL").UsedRange.Row + Worksheets("CV LOG_DETAIL").UsedRange.Rows.Count - 2

    FYI - There was no noted difference in the speed to run the macro. (Both ways took about 30 seconds)
    Regards,

    Gary
    (It's been a while!)

  14. #14
    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: Lost in Hiding Rows... (XL 2k SR1)

    BTW, How did you get your code to keep its formatting so it is readable? (I wonder, is readable a word?)
    Regards,

    Gary
    (It's been a while!)

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

    Re: Lost in Hiding Rows... (XL 2k SR1)

    I assume that you are talking about keeping readable format (indents) when posted in the forum. You need to put what are called pre tags around the code a pre tag looks like this:

    [ pre]
    <pre> your code goes here
    </pre>

    [ /pre]

    There is a space in the above between the left bracket and the "p" or the "/" so that it will actually display, you do not put that space in when actually using the tags.
    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

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