Is there a macro to hide rows?

June 19, 2011 at 02:57:18
Specs: Windows XP
I have a worksheet, used as a requisition. In column A, rows 12-78, there is a formula:

IF(ISNA(VLOOKUP('Dinner Production Schedule'!C14,RecipeNumbers!$B:$E,3,FALSE)),0,VLOOKUP('Dinner Production Schedule'!C14,RecipeNumbers!$B:$E,3,FALSE))

When the result returns a "0", I would like the row to be hidden. Any help is appreciated.


See More: Is there a macro to hide rows?

Report •

#1
June 19, 2011 at 06:46:14
You could use a Worksheet_Change macro.

Without knowing more about how your workbook is set up and where the changes that cause the zeros to appear are being made, the best I offer is some sample code...

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Row > 11 And Target.Row < 79 Then
   If Range("A" & Target.Row) = 0 Then
    Rows(Target.Row).Hidden = True
   Else: Rows(Target.Row).Hidden = False
   End If
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 19, 2011 at 11:30:50
I found this macro, that works perfect to hide the rows, now all i need to do is have the rows reappear when they have a value other than 0.


Sub Hide_Rows()
Dim RngCol As Range
Dim i As Range
Set RngCol = Range("A1:A78")
For Each i In RngCol
i.EntireRow.Hidden = False
If i.Value = 0 Then _
i.EntireRow.Hidden = True
Next i
End Sub


Report •

#3
June 19, 2011 at 13:56:06
You have to manually run the code that you posted each time you want hide/unhide rows.

Mine can run automatically each time the workbook is changed, hiding rows with zeros, and unhiding row without zeros.

Which do you want?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 19, 2011 at 22:45:24
DerbyDad03

I ran yours and it didn't do anything until i played with cells within the rows i want to hide and unhide.

Here is exactly what I have:

page 1 is titled LINES that is formatted to look like a serving line. it has cells with validation list of recipe names linked to a combobox.
page 2 has a list of recipe names and recipe numbers and I'm working on adding inventory names for each recipe.
page 3 is a production schedule with links to page one so the selected recipes automatically populate in the cells on the prduction schedule. the cells next to the recipe name on the production schedule have a vlookup formula to look up the recipe name from page 2 and return the recipe number. then there is a hyperlink to the pdf recipe cards so they can be printed from the same page.
page 4, the one i want to hide and unhide cells on, is a kitchen requisition. the cells that i want to hide, have the formula i posted before to look up the recipe name from the production schedule on page 3 from the recipe name page and return the ingredients for the recipe. Eventually, it will be linked to my inventory to automatically draw out.

All i need is for the cells to hide and unhide automatically. the zeros come when there is nothing in a particular cell on the production schedule, hence nothing to be ordered. Here is the formula again so you can look at it.


I have a worksheet, used as a requisition. In column A, rows 12-78, there is a formula:

IF(ISNA(VLOOKUP('Dinner Production Schedule'!C14,RecipeNumbers!$B:$E,3,FALSE)),0,VLOOKUP('Dinner Production Schedule'!C14,RecipeNumbers!$B:$E,3,FALSE))


Report •

Ask Question