I want to hide rows based on cells value from another sheet

I want to hide row 11 in sheet2 if cell N11 is blank (or a value of 0) in Sheet1

I want to hide row 12 in sheet2 if cell N12 is blank in Sheet1

and so on...

Any ideas?

Right-click the sheet tab of Sheet1.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range
    If Not Intersect(Range("N:N"), Target) Is Nothing Then
        For Each cel In Intersect(Range("N:N"), Target)
            Worksheets("Sheet2").Range(cel.Address).EntireRow.Hidden = _
                (cel.Value = 0)
        Next cel
    End If
End Sub

Switch back to Excel.

Save the workbook in a format that supports macros: .xlsm, .xlsb or .xls, but not .xlsx.

You'll have to allow macros when you open the workbook.

Wow you are good!  Thank you so much.

What if, rather than N11 being blank, but if it has a qty of 0 or less?  How would I code that?

I would also like the rows in Sheet2 to unhide of their value is greater than zero.

Thanks again Hans!

• Edited by rustava 11 hours 32 minutes ago

You could change

cel.Value = 0

to

cel.Value <= 0

Wow you are good!  Thank you so much.

What if, rather than N11 being blank, but if it has a qty of 0 or less?  How would I code that?

I would also like the rows in Sheet2 to unhide of their value is greater than zero.

Thanks again Hans!

• Edited by rustava Wednesday, March 11, 2015 7:50 PM

Sorry to keep bothering you Hans...but I would like it so if the word "Axis" appears in column C then the number 1.37 would auto populate in column H of the same row (on the same worksheet).

Like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Not Intersect(Range("N:N"), Target) Is Nothing Then
        For Each cel In Intersect(Range("N:N"), Target)
            Worksheets("Sheet2").Range(cel.Address).EntireRow.Hidden = _
                (cel.Value = 0)
        Next cel
    End If
    If Not Intersect(Range("C:C"), Target) Is Nothing Then
        For Each cel In Intersect(Range("C:C"), Target)
            If cel.Value = "Axis" Then
                cel.Offset(0, 5).Value = 1.37
            Else
                cel.ClearContents
            End If
        Next cel
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Hi Rustava,

Please try Mr. Hans's suggestion first, or if you have any further question about coding, I recommend you post the question to the MSDN forum for Excel

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

George Zhao
TechNet Community Support

Hello Hans...you have been so helpful thus far.  

They numerical values in all of column N's rows are an auto sum. For example, N11=SUM(C11:M11)

The only way your code works is if I delete the number in column N.  If the auto sum is equal to 0 (or less than 0), it does not hide the appropriate column in "Sheet2".

Any thoughts?

The request I had about having the number 1.37 populate in column H if the the word "Axis" appears in column C is actually on a totally different tab called "Bid"

Does this work better?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Not Intersect(Range("C:M"), Target) Is Nothing Then
        For Each cel In Intersect(Range("C:M"), Target).Rows
            Worksheets("Sheet2").Range("A" & cel.Row).EntireRow.Hidden = _
                (Range("N" & cel.Row).Value <= 0)
        Next cel
    End If
    If Not Intersect(Range("C:C"), Target) Is Nothing Then
        For Each cel In Intersect(Range("C:C"), Target)
            If cel.Value = "Axis" Then
                Worksheets("Bid").Range("H" & cel.Row).Value = 1.37
            Else
                Worksheets("Bid").Range("H" & cel.Row).ClearContents
            End If
        Next cel
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

That doesn't seem to do the trick.  Do I put that entire thing in the same VBA?  Or should they be 2 separate macros?

Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as Microsoft OneDrive (https://onedrive.live.com), FileDropper (http://filedropper.com) or DropBox (https://www.dropbox.com). Then post a link to the uploaded and shared file here.

Or register at www.eileenslounge.com (it's free) and start a thread in the Excel forum. You can attach files up to 250 KB to a post there (zipped if necessary).

OK...I just registered an account at eileenslounge.com.