Hiding Rows if a Value is Zero

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!

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?

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

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