Hide rows based on criteria

Topic

New Reply

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?

Reply | Quote

Replies

Something like thios might work:

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

Reply | Quote

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?

Reply | Quote

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)))

Reply | Quote

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”

Reply | Quote

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

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"

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.

Reply | Quote

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

Reply | Quote

Andrew,

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

Reply | Quote

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

Reply | Quote

*** 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.

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

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.

Reply | Quote

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.

Reply | Quote

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:

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

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Dean,
Thanks. However, being new to VBA, I don’t know how to do what you are saying I should do-would you mind showing me?
Thanks,
Jeff

Reply | Quote

It sounds like something like the code below will do what you want:

Public Sub HideIfWIsZero()
Dim I As Long, lLastRow As Long
    lLastRow = Worksheets("Sheet1").Range("W65536").End(xlUp).Row - 1
    For I = 0 To lLastRow
        If Worksheets("Sheet1").Range("W1").Offset(I, 0) = 0 Then
            Worksheets("Sheet1").Range("W1").Offset(I, 0).EntireRow.Hidden = True
        Else
            Worksheets("Sheet1").Range("W1").Offset(I, 0).EntireRow.Hidden = False
        End If
    Next I
End Sub

Reply | Quote

Sure (to the extent I can, via a message board )

In excel, open up the Visual Basic Editor, either by going through “Tools / Macro / Visual Basic Editor” or by typing Alt+F11. In the editor, click “Insert / Module” to insert a new, blank module.

In the module, type:

Option Explicit

Sub Compress()

Dim rngTestArea As Range
Dim rngCell As Range
Set rngTestArea = Selection

Application.ScreenUpdating = False

For Each rngCell In rngTestArea
    If IsNumeric(rngCell.Value) = True Then
        If rngCell.Value  0 Then
            rngCell.EntireRow.AutoFit
        Else
            rngCell.RowHeight = 0
        End If
    Else
        If rngCell.Value = "" Then
            rngCell.RowHeight = 0
        End If
    End If
Next

Application.ScreenUpdating = True

End Sub

You don’t have to worry about getting the indentation right, although it will make it easier to follow the code – use the tab button to indent and shift-tab to “outdent” lines as you type. If you are going to do a lot of coding, I would look for the “Smart Indenter” from Stephan Bullan at BMS (http://www.BMSLtd.ie[/url%5D). It would be nice if you could cut and paste from here, but it places all the code in one long line – it is possible to reorganize it, but it is a bit of a PITA

Work your way down through the code if you want to see how it works (I included an explanation the first time I posted it). In use, you highlight the portion of the column you want to deal with, then click “Tools / Macro / Macros” and select “compress” and run the macro.

Let me know how it works!

Edited Mar 13th 2004 to update link

Reply | Quote

[indent]

---

It would be nice if you could cut and paste from here, but it places all the code in one long line

---

[/indent]
Curiously though if you paste it into Word, then copy from Word it retains the formatting ok

Peter

Reply | Quote

[indent]

---

Curiously though if you paste it into Word, then copy from Word it retains the formatting ok

---

[/indent]

Hey – it’s one more step, but a step worth taking, I think.

Thanks for the tip!

Reply | Quote

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

    lCol = Selection.Column

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

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

Reply | Quote

Hi. -If- I understand what you are asking, I just want to annoy all the macro posters here by noting that there is at least one non-macro approach you could try. I also note that you -did- originally ask for a macro, but also you were a little uncomfiortable with tweaking macros. So this is just a FWIW.

Autofilter the dataset. To hide (for example) blank rows, filter on Blanks (it’s an option in parentheses at the bottom of the Autofilter drop-down) in the appropriate column. Select all the entire entire rows in data area of blanks (the row numbers will be in light blue in a standard Excel installation), down to the first row past the data are (the first row number that is black). Then select via menu Edit, Goto Special, Visible Cells. You will notice a border change showing that only the “blank cell rows” are selected. Right click any one of the selected visible rows, select hide. Then Autofilter for Non-blanks, all data will show except the blank rows.

Repeat as necessary to hide rows containing words such “Variance”. And you can get pretty sophisticated by using the Custom Filter option under the Autofilter dropdown.

This approach also can be turned into a macro, but the macros already provided are more effective.

Reply | Quote