Hiding Rows if a Value is Zero

**johnnycanuck** · 06-14-2011, 01:12 PM

I have a spreadsheet with multiple tabs, on each tab there are some rows (not in continuous ranges) that need to be hidden if the value in column F of that row is zero.

If the value in column F is non-zero, I want a dialog to pop-up indicating "Row # on worksheet Y is non-zero" and giving the user the option to 1) hide the row anyway, 2) highlight the row (make the cell in column A shaded red) or 3) cancel out of the macro.

In total there are 9 worksheets and about 100 rows to analyse.

I know I could create an if statement for each row that looks to F and either pops up the dialog or hides the row, but that would mean 100 if statement which strikes me as slow and inefficient to both code and execute.

I'm wondering if anyone can suggest a more efficient approach to this problem.

Thanks in advance!

**tarquinious** · 06-14-2011, 01:41 PM

I didn't know how to come up with a msgbox with 3 options for this, so instead created a simple form for you instead.

The macro starts at F1 and keeps looking down the column until a blank cell is found. Each time it finds a non-zero value the form pops up with the 3 options: Hide, Highlight, Ignore.

Hide makes the entire row hidden
Highlight makes the cell in column A have a red background
Ignore just hides the form and moves on.

Hopefully this is what you were looking for.

**Mordred** · 06-14-2011, 01:41 PM

Hi johnnycanuck, the following code will hide rows with a 0 in them

Sub RowHider()
    Dim wb As Workbook
    Dim shtCnt As Long, shtCntr As Long
    Dim StrtRng As Variant, EndRng As Variant, FullRng As Variant, cl As Variant
    Set wb = ActiveWorkbook
    shtCnt = wb.Sheets.Count
    For shtCntr = 1 To shtCnt
        With wb.Sheets(shtCntr)
            Set StrtRng = .Range("F2")
            Set EndRng = .Range("F10000").End(xlUp)
            Set FullRng = Range(StrtRng, EndRng)
            For Each cl In FullRng
                If cl.Value = 0 Then
                    cl.EntireRow.Hidden = True
                End If
            Next cl
        End With
    Next shtCntr
End Sub

As for your other requirements, it seems like a lot of dialog pop-ups, or can be a lot, for the user to decide on. At any rate, that will require a form with options on it, which may take a little while to design, at least for me it will. However, I can do it but I cannot promise you'll get it post-haste!

**Mordred** · 06-14-2011, 01:43 PM

Never mind me!

**johnnycanuck** · 06-14-2011, 02:24 PM

Hey guys, thanks for the very quick responses.

They are both good solutions but the issue I'm struggling with is that the rows I need to analyse and hide are not in a continuous range.

I.e. on one sheet, it is only rows 2, 3, 7, 9, 85, 89, 112-115, 189, 193-197, 200, 204, 205 that I need to look at and hide, if their F column value is 0. No matter what the value of row 1, 4, 5, 6, 8, 10-84, etc., even if it is 0, I don't want to hide it (or even look at it for that matter).

On another sheet, it is a different set of rows to analyse/hide.

Other than an if statement for each row, do you have any suggestions for an efficient way to loop through this disparate set of rows?

As for the dialog popping up a lot, it is expected that the rows will have a zero value so it *shouldn't* pop up often. (If it does, I expect the user to choose to cancel the process and resolve the underlying issue).

Thanks again

**Mordred** · 06-14-2011, 02:28 PM

I may be wrong but if you want to only look at a determined batch of rows ie: 2,3 7,9,85,89, & etc then you'll have to create a whack of if statements. Otherewise the code will cycle through and look for any row with the value of 0 and then deal with it.

If you want to be that specific, you'll most likely have to be that specific in your code.

**Mordred** · 06-14-2011, 02:32 PM

Perhaps a mock workbook with mock data and your desired outcome would be applicable here. Can you upload one?

**johnnycanuck** · 06-16-2011, 04:48 AM

Thanks again for your help and suggestions.

I ended up writing some code that utilizes GoSub to save me from having to write a custom IF statement for each row in my (long) list.

Attached is what I wrote. I'll need to add some error handling and status updating, but otherwise this gets the job done.

The form is just a simple 3-button dialog box that returns value 0, 1 or 2 based on the user's selection.

I know using GoTo (and presumably GoSub) and .Select code is poor form, but I couldn't come up with better alternatives. Suggestions to make the code more efficient, robust or just better would be appreciated and welcomed.

Thanks again

Sub AnalyseandHideMetalDetail()

Dim HideLead As String
Dim FormatforPrint As String

Dim DoForm As frmWhatToDo
Set DoForm = New frmWhatToDo

HideLead = Worksheets("Summary").Range("L7").Value
      
HideLeadSection:
    CurrentHide = HideLead
    
        Worksheets("Summary").Range("F98").Select
            GoSub Analyse
            
        Worksheets("Summary").Range("F105").Select
            GoSub Analyse
      
        Worksheets("Summary").Range("F196").Select
            GoSub Analyse
Exit Sub

Analyse:
        If ActiveCell.Value = 0 Then
            ActiveCell.EntireRow.Hidden = CurrentHide
            ElseIf CurrentHide = False Then GoSub WhatToDoForm
            Else: ActiveCell.EntireRow.Hidden = CurrentHide
        End If
    Return



WhatToDoForm:
  DoForm.labWhatToDo.Caption = "Row " & ActiveCell.Row & " on " & _
    ActiveSheet.Name & " is non-zero"
  DoForm.Show
    Select Case DoForm.Tag
        Case 0
            Unload DoForm
            Set DoForm = Nothing
            Exit Sub
        Case 1
 '          Debug.Print "You chose Don't Hide"
            Range("A" & ActiveCell.Row).Select
            ActiveCell.Interior.Color = 3
        Case 2
'           Debug.Print "You chose Hide Anyway"
            ActiveCell.EntireRow.Hidden = CurrentHide
        End Select
    Return

End Sub

**gjjh25** · 11-14-2011, 08:00 AM

Originally Posted by Mordred

Hi johnnycanuck, the following code will hide rows with a 0 in them[code]Sub RowHider()
Dim wb As Workbook!

I have just tried this code and it nearly does what i require, however there are rows at the top and bottom of the work sheet that i don't want removing, is there a way for this to only run a group of rows i select?