Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Location
    Midland, Texas, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hiding Rows with zero sum, with conditions (9.03821 SR-1)

    I have spent numerous hours searching every Excel forum I could locate trying to find something that resembles my problem. And, as many others that submit these questions, I am not a programmer, but I end up supporting my staff when they need help.
    Background.
    We have a spreadsheet we use to collect our monthly credit card charge information in order that the charges may be properly dispersed into various accounting codes. It has 266 rows and around 62 columns. A column or row may not contain any data for the month, therefore is not needed (maybe).
    Since the sheet is printed for accounting's use, we needed a way to optimize what is shown.

    I managed to find some routines that resembled what i needed to do, only the rows & columns were deleted, and we wanted only to hide them. I managed to re-write the column portion so if functions as we needed, hiding all columns that has a sum of zero. However I am having a problem with the rows. This requires a second condition in addition to the row sum =0, the rows containing a text string in column D needs to remain visible. Column A contains text in every row, column D contains formulas in every row except for the "section title" rows that have a text string of varying lengths. The range for the columns is f1:bs263 and the range for the rows is: d2:bs263.

    I have included the code I am currently working with. I commented out the line that I had tried to use for the second condition, since I get errors.

    Hopefully I have supplied sufficient information to allow someone to provide a solution. Any help is greatly appreciate.

    Private Sub Hiderows()
    'By Eddie Price 7-25-03
    ' hides empty rows except for Section Titles

    Dim NewSel As Range, area As Range, column As Range, colct As Range, row As Range, cold As Range
    Dim LastCol As Integer, lastRow As Integer

    Set colct = Range("iv1") ' this is a cell that counts the number of columns
    Set NewSel = Range("D1:Bs264")
    Set cold = Range("D") ' column that contains both text and formulas

    If TypeName(Selection) <> "Range" Then Exit Sub
    If Selection.Columns.Count Mod colct = 0 Then
    LastCol = Selection.SpecialCells(xlLastCell).column
    lastRow = Selection.SpecialCells(xlLastCell).row
    Set NewSel = Range(Cells(1, 1), Cells(LastCol, lastRow))
    End If
    Application.ScreenUpdating = False


    For Each area In NewSel.Areas
    For Each row In area.Rows
    'If Application.Selection.cold = IsNumber And
    If Application.Sum(row.EntireRow) = 0 Then row.EntireRow.Hidden = True ' this works, but also hides the rows that need to remain visible
    Next row
    Next area
    End Sub

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Hiding Rows with zero sum, with conditions (9.03821 SR-1)

    How about inserting a column, titled 'Hide', say, where you add a formula that evaluates to either 'Yes' or 'No' depending on whether you want the row hidden or not. Then add an autofilter (Data | Filter | AutoFilter) and select 'No' from the drop-down on the new column.

  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: Hiding Rows with zero sum, with conditions (9.03821 SR-1)

    I think you need something like, untested:

    If IsNumeric(Intersect(cold, row)).Value And

    alos, take out the If on the next line.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    New Lounger
    Join Date
    Jul 2003
    Location
    Midland, Texas, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Rows with zero sum, with conditions (9.03821 SR-1)

    Thanks a bunch. I tried your suggestion and the final code ended up as:

    If Application.Sum(row.EntireRow) = 0 And _
    Intersect(cold, row) = IsNumber Then row.EntireRow.Hidden = True

    This works!!
    Thanks again. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

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

    Re: Hiding Rows with zero sum, with conditions (9.03821 SR-1)

    Do you have Option Explicit set? (See VBA Help for the reasons why it's a good idea.) I don't think:

    Intersect(cold, row) = IsNumber Then row.EntireRow.Hidden = True

    is valid VBA code, I think you should be using:

    Isnumeric(Intersect(cold, row)) Then row.EntireRow.Hidden = True

    and in order to make the code more self-documenting, I would always use:

    Isnumeric(Intersect(cold, row).Value) Then row.EntireRow.Hidden = True
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    New Lounger
    Join Date
    Jul 2003
    Location
    Midland, Texas, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Rows with zero sum, with conditions (9.03821 SR-1)

    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> No, I didn't have the option explicit statement, but will from here on in!
    I tried the original code (you suggested) and it did't work, so I fooled around with it until I came up with my version and it worked.
    When I set the Option Explicit, my version failed as you suspected. Changed the code to your latest suggestion and, viola! It works.

    As a non-programmer, I usually just fumble around until something works, mostly working from an example in a book or on-line that somewhat resembles my situation. Generally I can come up with a solution, but this really had me stumped.

    I sometimes have difficulty understanding what is happening with the code as it is written, sort of like a blind man driving a car on the freeway at rush hour.

    Again, Thanks for the help. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  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: Hiding Rows with zero sum, with conditions (9.03821 SR-1)

    ... and I see that my original untested code has a paren in the wrong place, ")).Value" should have been ").Value)", apologies for my error.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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