Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 97 - Macro assistance please (Excel 97 / 2000)

    I have been asked by our finance team to create a macro but I am not sure of the vb code needed. So I pass it over to you guys etc.

    Is it possible to write a macro, VB script or something to automatically hide lines with a zero value in the formula in column H. Because each line has a formula in column H excel is unable to filter as, according to it, there is something in the cell, it doesn't matter that the value is zero.

    So need to hide any row where the content of column "H" = "0"

    Ideas please

    thank you from Carl Cross UK

  2. #2
    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: Excel 97 - Macro assistance please (Excel 97 / 2000)

    If you have filters set up you can set the custom filter to "not equal to" 0 and filter out the items without a macro.

    This macro will filter column H to "filter out the zeroes" (assumes you have a filter on all col A:h (at least)
    <pre>Sub FilterH()
    Range("h1").AutoFilter Field:=8, Criteria1:="<>0"
    End Sub</pre>



    If you still need a macro to actually HIDE the rows this should do it. I also included one to unhide them

    <pre>Sub HideH0()
    Dim rcell As Range
    Dim rng As Range
    Set rng = Range(Range("h1"), Range("h65536").End(xlUp))

    For Each rcell In rng
    If IsNumeric(rcell.Value) Then
    If rcell.Value = 0 Then _
    rcell.Rows.Hidden = True
    End If
    Next
    End Sub

    Sub UnhideH()
    Columns("h").Rows.Hidden = False
    End Sub</pre>


    Steve

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 - Macro assistance please (Excel 97 / 2000)

    Thats works fine. thank you

    If I want to ignore row 288 what is the amended code for that range.

    Thanks

  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: Excel 97 - Macro assistance please (Excel 97 / 2000)

    How about like this?

    <pre>Sub HideH0()
    Dim rcell As Range
    Dim rng As Range
    Set rng = Range(Range("h1"), Range("h65536").End(xlUp))

    For Each rcell In rng
    If IsNumeric(rcell.Value) And _
    rcell.Row <> 288 Then
    If rcell.Value = 0 Then _
    rcell.Rows.Hidden = True
    End If
    Next
    End Sub</pre>



    Steve

Posting Permissions

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