Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Another Row Hiding Macro (2k3)

    I'm writing a macro to hide rows if they contain a 0 (zero) in the column "S" This is what I'd like, but IsNA doesn't work in VBA like in excel:

    <pre>Sub Hide_ACV()
    Dim oCell As Range
    Dim zero As Integer

    zero = 0

    For Each oCell In Range(Range("S1"), Range("S65536").End(xlUp))
    If (IsNA(oCell) = False) And (oCell <> "") Then
    If Val(oCell) = zero Then
    oCell.EntireRow.Hidden = True
    End If
    End If
    Next oCell
    End Sub
    </pre>


    the reason i put the var zero in there is because it was erroring on blanks and #N/As. so then i added an IsNA() function which apparently doesn't work in VBA like it does in excel. I also don't want it to hide the row if there is NO value in it at all... If I take out the IsNA() function, the macro will actually run until it hits a #N/A.

    How can we make this work? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Another Row Hiding Macro (2k3)

    Try this:

    <code>
    Sub Hide_ACV()
    Dim oCell As Range
    Dim zero As Integer

    zero = 0

    For Each oCell In Range(Range("S1"), Range("S65536").End(xlUp))
    If (oCell.Value = CVErr(xlErrNA)) And (oCell <> "") Then
    If Val(oCell) = zero Then
    oCell.EntireRow.Hidden = True
    End If
    End If
    Next oCell
    End Sub
    </code>
    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Another Row Hiding Macro (2k3)

    You can use Application.WorksheetFunction.IsNA(...) in Excel VBA. The WorksheetFunction object makes many (but not all) worksheet functions available in VBA.

    Note: you'll have to rearrange your code slightly:

    If Application.WorksheetFunction.IsNA(oCell) = False Then
    If oCell <> "" Then
    ...
    End If
    End If

    If oCell contains an error value such as #N/A, the test oCell <> "" causes an error, so you must test for #N/A first.

  4. #4
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Row Hiding Macro (2k3)

    Hmm.. Now i get a type mismatch at this line:

    If (oCell.Value = CVErr(xlErrNA)) And (oCell <> "") Then

    any ideas?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Row Hiding Macro (2k3)

    Type mismatch as well... obviously both functions you and Legare gave me work, but I must have dim'd something incorrectly.

    should oCell not be "range"?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Another Row Hiding Macro (2k3)

    This version works for me:

    Sub Hide_ACV()
    Dim oCell As Range
    Dim zero As Integer
    zero = 0
    For Each oCell In Range(Range("S1"), Range("S65536").End(xlUp))
    If Application.WorksheetFunction.IsNA(oCell) = False Then
    If oCell <> "" Then
    If Val(oCell) = zero Then
    oCell.EntireRow.Hidden = True
    End If
    End If
    End If
    Next oCell
    End Sub

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

    Re: Another Row Hiding Macro (2k3)

    Does this work:

    <code>
    Option Explicit

    Sub Hide_ACV()
    Dim oCell As Range
    Dim zero As Integer

    zero = 0

    For Each oCell In Range(Range("S1"), Range("S65536").End(xlUp))
    If Not IsError(oCell) Then
    If (oCell.Value <> "") And (Val(oCell.Value) = 0) Then
    oCell.EntireRow.Hidden = True
    End If
    End If
    Next oCell
    End Sub
    </code>
    Legare Coleman

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

    Re: Another Row Hiding Macro (2k3)

    I think that version will also get the type mismatch if the cell contains any other error than #NA.
    Legare Coleman

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Another Row Hiding Macro (2k3)

    Certainly. He could use Application.WorksheetFunction.IsError instead of Application.WorksheetFunction.IsNA.

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

    Re: Another Row Hiding Macro (2k3)

    You don't need the Application.WorksheetFunction. with IsError. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Legare Coleman

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Another Row Hiding Macro (2k3)

    <img src=/S/razz.gif border=0 alt=razz width=25 height=17>

  12. #12
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Row Hiding Macro (2k3)

    Ah yeah that seems to take care of it.

    OK, last problem: the macro runs slower than my grandmother (she runs pretty slow <img src=/S/flee.gif border=0 alt=flee width=25 height=25>). The macro took about 30-45 seconds with 2500 lines of data. I remember seeing a "freeze screen" command that doesn't show the changes until they are all done, does this exist?

    thanks for the help so far.. it works, now lets see if we can speed it up! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Another Row Hiding Macro (2k3)

    Is this what you are looking for?

    <code>
    Option Explicit

    Sub Hide_ACV()
    Dim oCell As Range
    Dim zero As Integer

    zero = 0
    Application.ScreenUpdating = False
    For Each oCell In Range(Range("S1"), Range("S65536").End(xlUp))
    If Not IsError(oCell) Then
    If (oCell.Value <> "") And (Val(oCell.Value) = 0) Then
    oCell.EntireRow.Hidden = True
    End If
    End If
    Next oCell
    Application.ScreenUpdating = True
    End Sub

    </code>
    Legare Coleman

  14. #14
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Row Hiding Macro (2k3)

    Yes! And to prove how much of a difference that function makes, check out my test times:

    2500 lines of data
    Unfrozen Screen - 3m10s (avg)
    Frozen Screen - 1m (avg)

    Wow!

    Thanks to both of you <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  15. #15
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Row Hiding Macro (2k3)

    OK - not to be a pain or anything, but its hiding one of the title rows that has text in the cell: 'National ACV? ... any idea why?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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
  •