VBA Excel - Hide rows based upon multiple cell values

[Harry14]

Hi there,

First time poster, and beginner with VBA. I'm trying to write a code to hide/unhide rows based upon multiple criteria. I've managed to fumble my way through worksheet change events but can only get them to fire based on the last cell changed. Ultimately this is what I want to do, but I need the macro to evaluate all cells within the relevant range before deciding which rows to hide / unhide. My data is arranged as follows:

Range B8:V8 - Company name
Range B14: V14 - Accounting package (chosen from a validated drop down list)

If any cell in the range B14:V14 includes "Xero" I want the macro to unhide row 15. Conversely, if no cell in the range B14:V14 includes "Xero", I want row 15 to remain hidden.

I'm using a worksheet change event so the worksheet automatically triggers the hide/unhide action but it's not evaluating all results in B14:V14 hence the following conflict arises:
1. Cell B14 - if I select "Xero" in this cell, row 15 unhides which is the desired outcome; but
2 Cell C14 - if I select "MYOB" in this cell, row 15 then hides again which is not the aim (should remain unhidden based on B14 = "Xero")

I also tried creating a calculated flag at W14 to trigger a Worksheet_Calculate event which seemed to work fine, but when I added addition Worksheet_Calculate events the workbook crashed, I guess because it was triggering several macros at once.

Any help will be greatly appreciated. My code is below.

Cheers,

Harry



Private Sub Worksheet_Change(ByVal target As Range)

If target.Count > 1 Then Exit Sub
If Not Intersect(target, Range("B14:V14")) Is Nothing Then
Application.ScreenUpdating = False

Rows("15:15").EntireRow.Hidden = True

'To unhide rows 15:15 based upon selected criteria
Select Case target.Value
Case "Xero"
Rows("15:15").EntireRow.Hidden = False
Case Else

'Rows 15:15 remain hidden
End Select

Application.ScreenUpdating = True

End If

End Sub

 

[Arjan77]

You hide the row and then show it based on the value of the changed cell, you should address the total range instead of the target.value.

try this:

For Each cell In Range("B14:V14")
If cell.Value = "Xero" Then
Rows("15:15").EntireRow.Hidden = False
Exit Sub
End If
Next

 

[Harry14]

Genius! Thanks for your help Arjan. Taking your suggested approach 1 step further, I've added the below between 'End If' and 'Next'. It now goes both ways with hiding/unhiding.

If Cell.Value <> "Xero" Then
Rows("15:15").EntireRow.Hidden = True

Exit Sub

End If

Can't thank you enough!

 

[Harry14]

Sorry, scratch that...it only seems to be evaluating the first cell in the range. The code I have is below...any thoughts?



Private Sub Worksheet_Change(ByVal target As Range)

For Each Cell In Range("B14:V14")

If Cell.Value = "Xero" Then
Rows("15:15").EntireRow.Hidden = False

Exit Sub

End If

If Cell.Value <> "Xero" Then
Rows("15:15").EntireRow.Hidden = True

Exit Sub

End If

Next

End Sub

 

[Arjan77]

Harry,

Place the

Rows("15:15").EntireRow.Hidden = True

part after Next. So it looks like:

For Each cell In Range("B14:V14")
If cell.Value = "Xero" Then
Rows("15:15").EntireRow.Hidden = False
Exit Sub
End If
Next
Rows("15:15").EntireRow.Hidden = True

In this case if you match the criteria the sub is ended, if you don't match it the routine continues and hides the row.

Let me know if it does what you intended.

 

[Arjan77]

After reviewing your original code this would actually do the trick:

Private Sub Worksheet_Change(ByVal target As Range)

 If target.Count > 1 Then Exit Sub
 If Not Intersect(target, Range("B14:V14")) Is Nothing Then
 Application.ScreenUpdating = False

 Rows("15:15").EntireRow.Hidden = True

 For Each cell In Range("B14:V14")
If cell.Value = "Xero" Then
Rows("15:15").EntireRow.Hidden = False
Exit Sub
End If
Next

 Application.ScreenUpdating = True

 End If

 End Sub

It hides the row and unhides it if the criteria are met. So no need for extra code or enhancements.

 

[Harry14]

Great! Thanks for your help Artjan! Works perfectly...and looks much simpler than my original effort.

Harry,

Place the

Rows("15:15").EntireRow.Hidden = True

part after Next. So it looks like:

For Each cell In Range("B14:V14")
If cell.Value = "Xero" Then
Rows("15:15").EntireRow.Hidden = False
Exit Sub
End If
Next
Rows("15:15").EntireRow.Hidden = True

In this case if you match the criteria the sub is ended, if you don't match it the routine continues and hides the row.

Let me know if it does what you intended.