Hide row based on value (different sheets)

Challebjoern · #1 05-16-2017, 01:47 AM

Hey guys.

Been trying to write a formula (vba) to hide rows on sheet2 based on value in sheet1.
This is beacuse I use sheet1 to put in the data to make my business proposal which is shown in sheet2.

And since I don't always use all kind, it doesn't have to show the once who isn't offerd.

Code:

Sub HideRow()
    If Range("Blad1!E12").Value = 0 Then
        Rows("Blad2!10").EntireRow.Hidden = True
    Else
        Rows("Blad2!10").EntireRow.Hidden = False
    End If
End Sub

Tried using the one above but doesn't work. Also attached the workbook if you wanna take a look at it. Also, how would you write it to be hide more rows.

Hope you can crack this one for me!

NoSparks · #2 05-16-2017, 06:43 AM

Remove ALL the code you have and try putting this into the Blad1 module

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

'limit cells to monitor
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("E12:E17")) Is Nothing Then Exit Sub

Call HideRow

End Sub

Code:

Sub HideRow()

If Sheets("Kalkyl").Range("E12").Value = 0 Then
    Sheets("Färdig Offert-Vy").Rows("10").EntireRow.Hidden = True
Else
    Sheets("Färdig Offert-Vy").Rows("10").EntireRow.Hidden = False
End If

If Sheets("Kalkyl").Range("E13").Value = 0 Then
    Sheets("Färdig Offert-Vy").Rows("11").EntireRow.Hidden = True
Else
    Sheets("Färdig Offert-Vy").Rows("11").EntireRow.Hidden = False
End If

'etc to E17

End Sub

Challebjoern · #3 05-16-2017, 07:12 AM

Thanks alot!

It worked like a charm. 5 stars!
Also wrote the rest to make it work on all cells I want too.
___

Quote:

Originally Posted by NoSparks

Remove ALL the code you have and try putting this into the Blad1 module

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

'limit cells to monitor
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("E12:E17")) Is Nothing Then Exit Sub

Call HideRow

End Sub

Code:

Sub HideRow()

If Sheets("Kalkyl").Range("E12").Value = 0 Then
    Sheets("Färdig Offert-Vy").Rows("10").EntireRow.Hidden = True
Else
    Sheets("Färdig Offert-Vy").Rows("10").EntireRow.Hidden = False
End If

If Sheets("Kalkyl").Range("E13").Value = 0 Then
    Sheets("Färdig Offert-Vy").Rows("11").EntireRow.Hidden = True
Else
    Sheets("Färdig Offert-Vy").Rows("11").EntireRow.Hidden = False
End If

'etc to E17

End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Automatic Transfer of Data to Sheets Based on Drop-Down by VBA	STBTC	Excel Programming	0	03-25-2017 02:27 PM
How to Hide/Un-hide a worksheet based on cell on another sheet.	easton11	Excel Programming	1	06-02-2015 12:07 PM
Show/hide paragraphs based on upfront decisions	miscia76	Word	1	11-13-2013 04:29 PM
Sum Average usage Based on two sheets	bremen22	Excel	3	09-03-2013 11:27 AM
Hide Rows and Update Chart based on cell value	ubns	Excel Programming	5	05-07-2012 05:44 AM