Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Hide rows based on criteria

    I use a report which summarizes the data of over 100 departments. To make sure the departments balance I use sumif formulas two ways (once by job class and once by location). Simple subtration displays any variance between the two methods.

    When I print this report, I would like to hide the unnecessary rows, one extra total for sumif and the variance, which should be zero.

    I would like to employ a macro that searches for occurences of specific text and hides each row containint that text. The text, "Total By Job Class" or "Variance", would only occur in cells in one column. The user would position the cell pointer to begin the macro, since the starting position in the worksheet may vary.

    Is this possible and if so, can you demonstrate by example?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide rows based on criteria

    Something like thios might work:

    <pre>Dim i As Long
    For i = Selection.Row To 100
    If LCase$(Cells(i, 1)) = "variance" Then
    Rows(i).EntireRow.Hidden = True
    End If
    Next
    </pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Hide rows based on criteria

    Geoff,

    So in that worksheet in have inserted a module and now looks like this:
    ________________________________________________
    Sub Hide_rows_Var()


    Dim i As Long
    For i = Selection.Row To 100
    If LCase$(Cells(i, 1)) = "Variance" Then
    Rows(i).EntireRow.Hidden = True
    End If
    Next

    End Sub
    __________________________________________________ _

    Does not seem to do anything. I changed "v" to"V" in variance in case it's case sensitive.

    Also, is "Selection.Row To 100" designed to stop macro at 100 passes?

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide rows based on criteria

    Hi,

    Don't change v to V- the code compares lower case of the cell contents with the string (lCase$).

    The code assumes something in column 1. I don't know which column needs checking. Perhaps it should be the selection column?

    And yes, it does stop at 100. Again, I don't know the structure of your spreadsheet, so I don't know where you want to stop.

    I guess it was just seed code, to get you started.

    If it's not picking up the "variance" cell, it's possible that:
    .It's not in column 1
    .It's not in the top 100 cells
    .there's leading/trailing spaces. For the last, it's probable safer to check:
    trim$(lcase$(cells(i,1)))
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hide rows based on criteria

    Why not label the rows to be hidden (say "RowsToHide") and simply use the following :-

    Sub HideRows
    Application.Goto Reference:="RowsToHide"
    Selection.EntireColumn.Hidden = True
    End Sub

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Hide rows based on criteria

    Its a rather big worksheet, range to be evaluated begins at row 1112. I thought 100 began at current cell, not first row of worksheet, so I changed it to 2595. I've changed V back to v.

    Is "Cells(i, 1))" current column or first column to right of current cell?

    Must I pre-select a range with this code?

    When I run with cursor in current column and Cells(i, 1) or first column to left of column to be tested, there is no apparent reaction.

    If I change to Cells(i, 0) and place cursor back in current column, I get VB error, "Application-defined or object-defined error" and entire statement is highlighted "If LCase$(Cells(i, 0)) = "variance" Then"
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Hide rows based on criteria

    Andrew,

    These rows are not adjacent (and span over 1300 rows). Is the naming technique workable in that case?

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hide rows based on criteria

    The fact that the rows are non adjacent should make no difference. It is possible to create a single range from non-contiguous cells. For example you can select rows 3, 10, 15 etc, and name them as one range and then treat them in Excel or VBA as any other range.

    Not sure if that is what you aree looking for though.

    regards

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Hide rows based on criteria

    Legare,

    Just now tried you macro. I had to insert a few closing parens, but editing macros like your's or Goeff's is well beyond my current level of expertise.

    When I ran you macro it erred at 1Col = Selection.col, displaying the message, "Object doesn't support this property or method".

    To answer your questions:
    1) I haven't considered what should stop the macro. The report is not a fixed length in that job classes may be added or deleted from some of the departments. Other data or reports may be added to the bottom of the sample attached. Perhaps it would be best to preselect a range in a column prior to running. There are blank rows between locations in the report (see attached file).
    2) The column (H) to be tested would not vary, unless I am required to modify the report (unlikely).

    The sample attached is the report range I have added to the 900 rows (or more) of data above it summing with Sumif. The first row to be tested would be row 1267. I had to delete report cols to right and over half of the rows to fit the 100k attachment limit.
    Attached Files Attached Files

  10. #10
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hide rows based on criteria

    Arcturus:

    I have three suggestions:

    1) If all you want to do is hide the rows with a blank in a specified column, I would suggest the following VBA code:

    <pre><font color=blue>
    Sub Compress()
    Dim TestArea As Range
    Dim TestValue As Currency
    Set TestArea = Selection
    Application.ScreenUpdating = False
    For Each Cell In TestArea
    If IsNumeric(Cell.Value) = True Then
    TestValue = Selection.Value
    If TestValue <> 0 Then
    Cell.EntireRow.AutoFit
    Else
    Cell.RowHeight = 0
    End If
    Else
    If Cell.Value = "" Then
    Cell.RowHeight = 0
    End If
    End If
    Next Cell
    Application.ScreenUpdating = True
    End Sub
    </font color=blue></pre>


    Select the column you are using to test whether the row should be hidden, then run the macro.

    2) For the volume of data you have, you might also investigate whether a pivot table provides a better report format. you might have to manipulate the source data a bit to fill the blank values in some rows (or use tags like the division # rather than division, since the division field is left blank in several rows). I have filled in some random values for 'regular hours' as an example and attached a pivot table to your sample. In part, this will depend on how you are obtaining your data, particularly the budget and variance calculations, and at what level of detail they are calculated.

    3) If you have to trim details out of your s/sheet to meet the 100K limit, you might try 'zipping' the file with any one of the zip compression utilities. The attached file has been zipped - if you cannot open it let me know, and I will send it as a self-extractor.

    Dean
    Attached Files Attached Files

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

    Re: Hide rows based on criteria

    Part of that line got lost when I pasted that code into the message. That line should have read:

    <pre> lCol = Selection.Column
    </pre>


    If you want to select all of the cells to check, then the following code should work:

    <pre>Dim oCCell As Range
    For Each oCCell In Selection
    If (Trim(LCase(Cells(lRow,lCol) = "total by job class") Or _
    (Trim(LCase(Cells(lRow,lCol) = "variance")) Then
    oCCell.Entirerow.Hidden = True
    End If
    Next oCCell
    </pre>

    Legare Coleman

  12. #12
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide rows based on criteria

    Hi,

    Here's a slightly modified version of the code- it first finds the last row in the range with data before starting the loop

    <pre>Dim i As Long
    Dim lLastRow As Long

    ' Find the last row
    lLastRow = ActiveSheet.Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

    ' Start with the first row in the selection; go to the last
    ' row which contains data
    For i = Selection.Row To lLastRow

    ' look in the cell Row "i", column 2
    If LCase$(Cells(i, 2)) = "variance" Then
    Rows(i).EntireRow.Hidden = True
    End If
    Next

    msgBox "Finished the search"
    </pre>


    cells(i,1) is row i, column 1

    If you want to start looking down a selected column instead, you might code:
    Cells(i, Selection.column)

    You can code a hardwired range if you want, which will not depend on any selection.

    You had no apparent reaction because the data was in column 2, and the code was looking in column 1. I've added a MsgBox to give confirmation that something was run.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

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

    Re: Hide rows based on criteria

    *** Geoff W- edited to split long lines which cause problems when in "pre" tags" ***
    You left a couple of critical things out of your question:

    1- What determines when the macro should stop looking? Can it look until it finds the first empty cell in the column, or are there empty cells before the end?

    2- What column is the test string in? The column of the current selection, or some fixed column?

    The following code will hide rows containing either of the strings starting at the row of the current selection and searching until the first empty cell in the current selection column.

    <pre>Dim lRow As Long, lCol As Long
    lRow = Selection.Row
    lCol = Selection.Col
    While Cells(lRow, lCol) <> ""
    If (Trim(LCase(Cells(lRow,lCol) = "total by job class") Or _
    (Trim(LCase(Cells(lRow,lCol) = "variance")) Then
    Rows(lRow).EntireRow.Hidden = True
    End If
    lRow = lRow + 1
    Wend
    </pre>


    This code was not tested. If you need to search some other column, or use some other criteria to stop the search, then this will have to be modified.
    Legare Coleman

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hide rows based on criteria

    Dean,
    So, would this work if, say, I want a row hidden if the value in a particular cell in that row, say "W", was, say, zero? If so, would the code need any editing?
    Thanks,
    Jeff

  15. #15
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hide rows based on criteria

    Jeff - yes, it would work.

    You would start by selecting the column you want to examine - either the whole column, or more typically just a range of several rows to several hundred rows. The routine loops through all the cells, tests if they are numeric, and if so, if their value is "0." Rows with a zero value are compressed down to zero height, but are not actually deleted.

    In looking at the code again, I see that I didn't declare the variable "Cell" - you should probably amend it to include a declaration of "Cell" as a range object. It will still run as-is, but will fail if you have "option explict" set.

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
  •