VBA Conditional Row Delete Question

[strathybananas]

Hey Guys,

I am currently creating a programme which uses a drop down box, and I want to get rid of different options depending on the box selected.

However, I am having trouble getting rid of the rows which contain 'HIDE', which I have labelled as needing to be hidden (row height = 0).

I have been using a borrowed VBA code which gets rid of the 'HIDE' rows, but won't return them for the relevent boxes! (if that makes sense?!)

The code is:


Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
' Dim R As Range
' Set R = Application.Intersect(Target, Range("A1:E50"))
' If R Is Nothing Then Exit Sub
With ActiveSheet
For Each cell In Range("A1:E50")
If cell.Value = "HIDE" Then
cell.EntireRow.Hidden = True
End If
Next
End With
Application.ScreenUpdating = True
End Sub


Essentially, what I'm asking is how can I alter this code to return my 'HIDE' boxes when they no longer =HIDE?

Thanks,

Johnnie

 

[strathybananas]

I have put this into the general section, as opposed to the worksheets.

However, it is coming back as Compile Error: Invalid outside procedure and highlighting the "C2" in the first line of the code.

Any idea where I am going wrong?

 

[VoG]

You'll need to top and tail it

Sub test()
If Sheets("Origin").Range("C2").Value = "HIDE" Then
    Sheets("Area").Visible = xlSheetHidden
    Sheets("Ocean").Visible = xlSheetHidden
End If
End Sub

 

[strathybananas]

I tried using this:

Sub test()
If Sheets("Origin").Range("C2").Value = "HIDE" Then
Sheets("Area").Visible = xlSheetHidden
Sheets("Ocean").Visible = xlSheetHidden
Else If
If Sheets("Origin").Range("C2").Value = "5" Then
Sheets("Area").Visible = xlSheetunHidden
Sheets("Ocean").Visible = xlSheetunHidden
Sheets("Land").Visible = xlSheetHidden
End If
End Sub

But it wouldn't work?

Because the Area and Ocean sheets won't return when the value of C2 on Origin isn't "HIDE", and I need another sheet to hide when C2 is 5.

Any ideas?

 

[VoG]

Try

Sub test()
If Sheets("Origin").Range("C2").Value = "HIDE" Then
    Sheets("Area").Visible = xlSheetHidden
    Sheets("Ocean").Visible = xlSheetHidden
ElseIf Sheets("Origin").Range("C2").Value = 5 Then
    Sheets("Area").Visible = xlSheetVisible
    Sheets("Ocean").Visible = xlSheetVisible
    Sheets("Land").Visible = xlSheetHidden
End If
End Sub

 

[strathybananas]

I've tried putting the code on the same sheet as another code, and it just then ignores both of them.

If I put it on another sheet, it still isn't making the sheets that were hidden for the HIDE cell visible.

Any idea where to go from here as I can't get the hidden sheets back at all.

Thanks,

Johnnie

 

[VoG]

That code goes in a regular module: in the Visual Basic Editor select Module from the Insert menu then paste in the code. Press ALT + Q to close the code window, press ALT + F8, click on Test then click the Run button.

 

[strathybananas]

The same problem keeps occurring.

Is it possible that because the C2 box is a drop down menu, it will cause the code not to work?

Like the same sheets remain hidden regardless of whats in the box. Even when I get rid of all the code, those sheets are staying hidden. I can't get them to come back, let alone get the visible sheets to disappear for other options from that drop down box.

Any ideas whats happening?

Many thanks,

Johnnie

 

[VoG]

Maybe it should be

Sub test()
If Sheets("Origin").Range("C2").Value = "HIDE" Then
    Sheets("Area").Visible = xlSheetHidden
    Sheets("Ocean").Visible = xlSheetHidden
Else
    Sheets("Area").Visible = xlSheetVisible
    Sheets("Ocean").Visible = xlSheetVisible
    Sheets("Land").Visible = xlSheetHidden
End If
End Sub

This won't run automatically - you have to actively run the macro.