+ Reply to Thread
Results 1 to 8 of 8

Hiding rows depending on data

  1. #1
    Forum Contributor
    Join Date
    05-06-2004
    Posts
    117

    Hiding rows depending on data

    Greetings to all and thank you in advance for your help. I have a macro that runs a couple of procedures. The first procedure looks at the data in column 73 and hides certain rows depending on whether 0 is present. This macro works. However, I cannot get the second macro to work. After hiding the rows that show no data, I wanted to hide only certain rows if the range "SPINTOTAL" (which is the same as column 73) shows data other than 0
    I thought maybe a "DoWhile loop" would work, but I cannot seem to get the right code. Any suggestion would be greatly appreciated. Thank you
    Sub hiderow()

    Dim x As Integer

    Application.ScreenUpdating = True

    Calculate
    For x = 9 To 107

    If Cells(x, 73) = "0" Then
    Rows(x - 6).Hidden = True
    Rows(x - 5).Hidden = True
    Rows(x - 4).Hidden = True
    Rows(x - 3).Hidden = True
    Rows(x - 2).Hidden = True
    Rows(x - 1).Hidden = True
    Rows(x).Hidden = True

    End If
    x = x + 0
    Next x
    'Call hiderowwithnumber

    Application.ScreenUpdating = True

    End Sub


    Public Sub hiderowwithnumber()
    Dim SPINTOTAL As Variant
    Application.ScreenUpdating = True

    For Each cell In Sheets("1").Range("SPINTOTAL")
    Do While (cell, 73)<> "0"
    Rows(x - 6).Hidden = True
    Rows(x - 5).Hidden = True
    Rows(x - 3).Hidden = True
    Rows(x - 2).Hidden = True
    Rows(x).Hidden = False
    Loop
    Next cell
    Application.ScreenUpdating = True

    End Sub

  2. #2
    Forum Contributor starryknight64's Avatar
    Join Date
    09-27-2006
    Location
    Missouri
    MS-Off Ver
    2003 (Work) & 2007 (Home)
    Posts
    193
    This might not help very much or, maybe, not at all, but your code could use some "tidying up" to avoid any errors that could arise.

    Try putting your two functions together into one function via an "if ... else" statement.

    AKA...

    Please Login or Register  to view this content.
    starryknight64

  3. #3
    Forum Contributor
    Join Date
    05-06-2004
    Posts
    117
    Hello starryknight,

    Thank you for the suggestion. I thought about cleaning the code with an "if..else" statment. The only thing that stopped me was that I thought that the statement would only give me an either/or option to run the procedures when I would like to run both procedures. I would like to hide all the rows that show a total of 0 on column 73 as well as only certain rows for the group of cells that have a total different from 0 on column 73. Does this make sense?
    Thank you nonetheless for taking the time to look at my code and making improvements to it.

  4. #4
    Forum Contributor starryknight64's Avatar
    Join Date
    09-27-2006
    Location
    Missouri
    MS-Off Ver
    2003 (Work) & 2007 (Home)
    Posts
    193
    I'm gonna put this into my own words. Let me know if I am interpreting what you have specified correctly...
    1. Start at row 9.
    2. Column 73 is the column of interest filled with numbers.
    3. If any cell in column 73 is "0", then the cell's row as well as the previous 6 rows become hidden.
    4. If any cell in cloumn 73 is not "0", then the cell's row is shown and the 2nd, 3rd, 5th, and 6th previous rows become hidden.
    5. Goto the next row.
    6. Repeat steps 3 through 5 till we reach row 107.
    This is what I translated from your code. Is this what you want the macro to do?

  5. #5
    Forum Contributor
    Join Date
    05-06-2004
    Posts
    117
    Starryknight,

    You are correct in your interpretation. I neglected to add that the macro where is the data shows "0" and the 6 rows are hidden, only goes to row 107.
    However, when this happens I am left with the data that does not show "0" in column 73. These are the rows that I want to modify next. I only need to hide some of the rows for this group of data.
    Again, with my very limited knowledge of VB, I thought maybe running a "dowhile...."loop would do the trick. But, if you can think of a better solution, it would be an opportunity for me to learn.

    Thank you so much

  6. #6
    Forum Contributor starryknight64's Avatar
    Join Date
    09-27-2006
    Location
    Missouri
    MS-Off Ver
    2003 (Work) & 2007 (Home)
    Posts
    193

    Thumbs up

    Then, the code I have put together above should work as intended. Have you tried it yet? Please let me know because we just confirmed that what you explained and what I interpreted was the same thing.

    I think what may be troubling you is the fact that it is all in one function. This should not matter unless you use the "hiderowwithnumber" function seperately from the "hiderow" function somewhere else. By putting the two together into one function, under one loop, and under an "if...else" statement, we have decreased computation time by half. Now, I realize this might be of no importance seeing as how 25 ms takes just as long as 50 ms in human time. However, having them both in one function should not be a problem unless you really do use them seperately throughout your spreadsheet.

    Let me know if it works out!

  7. #7
    Forum Contributor
    Join Date
    05-06-2004
    Posts
    117
    Hello Starryknight,

    Thank you for the code. I tried it and it seems to hide all the rows except for the last. I am trying to figure out why this might be. I am thinking of maybe pointing one of the procedures in the loop to look at the totals in column 73 and the other to look at column 74, that way, each procedure will be triggered by diffirent circumnstances. What do you think about this?

  8. #8
    Forum Contributor starryknight64's Avatar
    Join Date
    09-27-2006
    Location
    Missouri
    MS-Off Ver
    2003 (Work) & 2007 (Home)
    Posts
    193
    This is probably because the cell(107,73) is not zero and, thus, is causing the last part of the "if...else" statement to happen. That is, "Rows(x).Hidden = False". Which means that the last row will not be hidden if its corresponding cell is non-zero. I assume now that you do not want this to happen. So simply change that to "Rows(x).Hidden = True" and you should be golden.

    If this doesn't work we're gonna have to delve deeper into this, which could get sticky.

    I hope this helps!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1