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

 

[VoG]

Try

For Each cell In Range("A1:E50")
    cell.EntireRow.Hidden = cell.Value = "HIDE"
Next

 

[strathybananas]

VoG,

I am quite new to VBA and I am not sure where to place the code you supplied me?

Thanks,

Johnnie

 

[VoG]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Range("A1:E50")
    cell.EntireRow.Hidden = cell.Value = "HIDE"
Next cell
Application.ScreenUpdating = True
End Sub

 

[strathybananas]

VoG,

That hides the first 'HIDE' cell I have, but the rest of them are still there? Do I need to loop the code to allow for excel to hide all of the 'HIDE' cells between A:1 and E:50?

 

[VoG]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Application.ScreenUpdating = False
For i = 1 To 50
    Rows(i).Hidden = WorksheetFunction.CountIf(Range("A" & i & ":E" & i), "HIDE") > 0
Next i
Application.ScreenUpdating = True
End Sub

 

[strathybananas]

VoG,

Thanks so much for the help!

I had one final question, which was if I was to change it from hiding rows to hiding other sheets depending on the values in the cells, would this be possible?

Say I had a value on Sheet1 which, when at HIDE, would hide Sheet 3 and 4.

What would I input for that?

Thanks,

Johnnie

 

[VoG]

Not terribly clear. Maybe

If Sheets("Sheet1").Range("A1").Value = "Hide" Then
    Sheets("Sheet3").Visible = xlSheetHidden
    Sheets("Sheet4").Visible = xlSheetHidden
End If

 

[strathybananas]

Ok so the value C2 of sheet, Origin, is HIDE.

I want to hide sheet, Area, as well as sheet, Ocean.

Is that hopefully a bit clearer?

Thanks!

Johnnie

 

[VoG]

Try

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