Code to hide row if no value

[Gomesm]

I have a group of cells merge "C23:I23" which get value from N9 (which has a Vlookup formula) I want row 23 to hide when no return comes to N9.

Can anyone help?

[Paul_Hossler]

In the worksheet code module, try using the Change event handler




Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If IsError(Me.Range("N9").Value) Then
Me.Rows(23).Hidden = True

ElseIf Len(Me.Range("N9").Value) = 0 Then
Me.Rows(23).Hidden = True

Else
Me.Rows(23).Hidden = False

End If

End Sub



I did a test where N9 is the result of a VLookup()

[Gomesm]

In the worksheet code module, try using the Change event handler




Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If IsError(Me.Range("N9").Value) Then
Me.Rows(23).Hidden = True

ElseIf Len(Me.Range("N9").Value) = 0 Then
Me.Rows(23).Hidden = True

Else
Me.Rows(23).Hidden = False

End If

End Sub



I did a test where N9 is the result of a VLookup()

Hi Paul,

On your page woks fine but vlookup value comes from a election another sheet. I change the vlookup value for another sheet on you worksheet and realised it also stops working .

I have a sheet lets call it sheet1 where I have a dropdown box with options, on sheet2 I have the vlookup value (N9) from the drop down o sheet1. On the same sheet2 I have on row 23 a link to the vlookup value from shee2 N9.I need row 23 to hide if no value is selected on Sheet 1 drop down box, thus not populated N9 on sheet2.

I initially had this code , and it worked but but because I have it on sheets it gave me an error 28 on no stack space:

Private Sub Worksheet_Calculate()
Range("A22:A22").EntireRow.Hidden = (Range("N9").Value = "")
Range("A23:A23").EntireRow.Hidden = (Range("N10").Value = "")
Range("A24:A24").EntireRow.Hidden = (Range("N11").Value = "")
End Sub

[Paul_Hossler]

Try something like this in the ThisWorkbook code module

Sheet1 is the one where row 23 is hidden based on N9 on Sheet2 which is where the VLookup() is




Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Not Sh Is Sheet2 Then Exit Sub

If IsError(Sh.Range("N9").Value) Then
Sheet1.Rows(23).Hidden = True

ElseIf Len(Sh.Range("N9").Value) = 0 Then
Sheet1.Rows(23).Hidden = True
Else
Sheet1.Rows(23).Hidden = False
End If
End Sub

[Gomesm]

Some how this code does not work. I am going o explain in better terms hat exactly I need.
I have a sheet called "Case Files" with 3 dropdown boxes on cells D23, D24 and D25.


I have another sheet called “Magistrate” with VLOOKUP codes oncells N9,N10,N11 with data from the drop down boxes from Sheet “Case File”.


Cells N9, N10 and N11 fill in rows 18, 19 and 20respectively.
What I need is every time any of the drop boxes is empty (Blank)to hide the respective rows (18,19.and 20) on sheet “Magistrate”

See file attached, although vlookup is no working tis is the idea

[Paul_Hossler]

1. I changed your VLookup()s

2. In the Magestrate code page.




Option Explicit

Private Sub Worksheet_Calculate()
Dim i As Long
Application.EnableEvents = False
For i = 18 To 20
Rows(i).Hidden = (Len(Trim(Cells(i, 2).Value)) = 0)
Next
Application.EnableEvents = True
End Sub

[Gomesm]

1. I changed your VLookup()s

2. In the Magestrate code page.




Option Explicit

Private Sub Worksheet_Calculate()
Dim i As Long
Application.EnableEvents = False
For i = 18 To 20
Rows(i).Hidden = (Len(Trim(Cells(i, 2).Value)) = 0)
Next
Application.EnableEvents = True
End Sub



Paul

Thank you for you help and sheet provided which works perfectly.

When I change my sheet, including the formulas, it hides all 3 rows and it does not unhide even when selected any field. What am I doing wrong?

the only thing I change on m vlookup formula was what you have changed, so on mine shows like these:
=IF('Case Details'!D23="","",VLOOKUP('Case Details'!D23,'Case Details'!$AA:$AI,9,FALSE))

and on your VBA code I changed it like this:

Option Explicit
Private Sub Worksheet_Calculate()
Dim i As Long
Application.EnableEvents = False
For i = 18 To 20
Rows(i).Hidden = (Len(Trim(Cells(i, 9).Value)) = 0)
Next
Application.EnableEvents = True

End Sub

[Gomesm]

tried with code as you suggested but it doesn't work either. As soon as I make a changes on the dropdown boxes on sheet "Case Files" it hides all 3 rows in "Magistrates" sheet and does not unhide them again.

Option Explicit

Private Sub Worksheet_Calculate()
Dim i As Long
Application.EnableEvents = False
For i = 18 To 20
Rows(i).Hidden = (Len(Trim(Cells(i, 2).Value)) = 0)
Next
Application.EnableEvents = True
End Sub

[Paul_Hossler]

Application.Calculate must be on Automatic and Application.EnableEvents must = True. It is turned off in my event macro only for the time to hide or show 3 rows

The macro used the Calculate event on the Magistrate sheet

If you delete D24 on the Case Details sheet, the VLookup on the Magistrate sheet in N8 is calculated (and = "") and B19 (=N8 and hence also = "") is calculated

This triggers the event and if B19 on the Magistrate sheet Len = 0 row 19 on the Magistrate sheet is hidden

I thought that was the goal??

If you changed the layout, things might not work

Look at the revised attachment

[Gomesm]

Paul
Your sheet is absolutely correct and exactly what I need. However when I apply it exactly to mine it hides all 3 rows and does not un-hide it. Not sure what is affecting it.
Can I please email it so you could kindly have a look, there must b something affecting it. I do not want to post it on he forum as it is an official document.
Your help is very much appreciated.