Hiding rows issue

[Jemmi]

End goal:
To enter a numeric value in a cell, and according to the value, it will hide all rows based on different values in a set column.

Example:
Enter 4 in cell B2 and it will hide all rows which contain values "AB5, AB6, AB7, AB8" in column P.

What i've tried:
VBA code, but i can only get it to work through defining which rows I want to hide, which is a problem as I have over 16000 rows of data. I'd like it to work dynamically dependant on the value in column P:

[TABLE="width: 65"]
<tbody>[TR]
[TD]Sub worksheet_change(ByVal target As Range)[/TD]
[/TR]
[TR]
[TD]If Not Intersect(target, Range("B2")) Is Nothing Then[/TD]
[/TR]
[TR]
[TD]    ActiveSheet.Rows("10:20000").EntireRow.Hidden = False[/TD]
[/TR]
[TR]
[TD]    If target = 1 Then[/TD]
[/TR]
[TR]
[TD]        ActiveSheet.Rows("12:18").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD]    ElseIf target = 2 Then[/TD]
[/TR]
[TR]
[TD]        ActiveSheet.Rows("13:18").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD]    ElseIf target = 3 Then[/TD]
[/TR]
[TR]
[TD]        ActiveSheet.Rows("14:18").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD]    ElseIf target = 4 Then[/TD]
[/TR]
[TR]
[TD]        ActiveSheet.Rows("15:18").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD]    ElseIf target = 5 Then[/TD]
[/TR]
[TR]
[TD]        ActiveSheet.Rows("16:18").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD]    ElseIf target = 6 Then[/TD]
[/TR]
[TR]
[TD]        ActiveSheet.Rows("17:18").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD]    ElseIf target = 7 Then[/TD]
[/TR]
[TR]
[TD]    End If[/TD]
[/TR]
</tbody>[/TABLE]

Conditional formatting may be a solution I haven't looked into yet, but really i'd to be able to type in a number and I see the information I need, enabling me to copy this to another sheet/document. I'd really appreciate any pointers on the best way of doing this, thanks.

 

[Fluff]

Does col P only contain certain values & if so how many different values could there be?

 

[Joe4]

I am little confused by this.

You say this:

Enter 4 in cell B2 and it will hide all rows which contain values "AB5, AB6, AB7, AB8" in column P.

but the code is not looking at the values, it is hiding specific rows, irrespective of their values:

ElseIf target = 4 Then

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

<tbody>
</tbody>

So I am not quite sure what the required logic is.
How many different values can be entered in cell B2?
Is the logic for each one predicable (based on some logic)?
If not, how is it determined which values or rows should be hidden?

 

[Jemmi]

Does col P only contain certain values & if so how many different values could there be?

Yes it does, which made me pick on that column! It has 1 unique value every 29 rows that can be ignored, and the rest of the 28 are repeated. e.g AB5, AB6, AB7, AB8 etc. Does that make sense?

 

[Jemmi]

I am little confused by this.

You say this:

but the code is not looking at the values, it is hiding specific rows, irrespective of their values:

So I am not quite sure what the required logic is.
How many different values can be entered in cell B2?
Is the logic for each one predicable (based on some logic)?
If not, how is it determined which values or rows should be hidden?

I didn't get that far. This code is looking at the value in B2 and hiding rows based on that. This won't work as I'd have to repeat this code 555 times amending the rows it hides for each one. Hence why I'd like to hide rows based on a value in column P now, but still based on what i enter in B2. I'm sharing what I did so you can see what i'm trying thus far. Thanks.

 

[Joe4]

Forgetting what you have tried so far, can you lay out the details of the logic rules that need to be programmed?
Include all ranges to be considered, what all the possible options for B2 are, and what should happen in each instance.
(One example probably isn't enough, unless there is a pattern, but we would typically need to see at least two examples to detect a pattern, if you don't spell that pattern out for us).

Remember, while your problem is familiar to you, it is not to us. We cannot see your data. The only thing that we have to go on is what you tell us in this thread.
The more detailed the explanation, the more likely the solution will work for you with minimal adjustments on your part.

 

[Jemmi]

Forgetting what you have tried so far, can you lay out the details of the logic rules that need to be programmed?
Include all ranges to be considered, what all the possible options for B2 are, and what should happen in each instance.
(One example probably isn't enough, unless there is a pattern, but we would typically need to see at least two examples to detect a pattern, if you don't spell that pattern out for us).

Remember, while your problem is familiar to you, it is not to us. We cannot see your data. The only thing that we have to go on is what you tell us in this thread.
The more detailed the explanation, the more likely the solution will work for you with minimal adjustments on your part.

Of course Joe, thank you!

A user selects or types a value in B2, in this case it is 2, this would then need to trigger a rule which will hide all rows based on particular values, the ones crossed out below are those:

COLUMN B				COLUMN P
Enter number here				Path
2				1
				AB1
				AB2
				<s>AB3</s>
				<s>AB4</s>
				QW1
				QW2
				QW3
				QW4
				<s>QW5</s>
				<s>QW6</s>
				<s>QW7</s>
				<s>QW8</s>
				2
				AB1
				AB2
				<s>AB3</s>
				<s>AB4</s>
				QW1
				QW2
				QW3
				QW4
				<s>QW5</s>
				<s>QW6</s>
				<s>QW7</s>
				<s>QW8</s>
				3
				AB1
				AB2
				<s>AB3</s>
				<s>AB4</s>
				QW1
				QW2
				QW3
				QW4
				<s>QW5</s>
				<s>QW6</s>
				<s>QW7</s>
				<s>QW8</s>

<colgroup><col style="mso-width-source:userset;mso-width-alt:4096;width:87pt" width="116"> <col style="width:49pt" width="65" span="3"> <col style="mso-width-source:userset;mso-width-alt:2528;width:54pt" width="72"> </colgroup><tbody>
</tbody>

If the user enters 2 in B2, entire rows which contain the values AB3, AB4, QW5, QW6, QW7, QW8 in column P will need to be hidden. I hope that's a lot more clear now, thanks.

 

[Fluff]

Do any of your values go above 9, so could you have AB10 or QW10?

 

[Jemmi]

Do any of your values go above 9, so could you have AB10 or QW10?

That's a possibility yes but not something i've put in for now. For every AB there are two QW.

 

[Fluff]

In that case maybe something like

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Ary As Variant, Ary2 As Variant
   Dim i As Long
   
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B2" Then
      Ary = Array("1", "2", "3", "AB1", "AB2", "AB3", "AB4", "QW1", "QW2", "QW3", "QW4", "QW5", "QW6", "QW7", "QW8")
      With CreateObject("scripting.dictionary")
         For i = 0 To UBound(Ary)
            .Item(Ary(i)) = Empty
         Next i
         Select Case Target
            Case 1: Ary2 = Array()
            Case 2: Ary2 = Array("AB3", "AB4", "QW5", "QW6", "QW7", "QW8")
            Case 3: Ary2 = Array()
            Case 4: Ary2 = Array()
         End Select
         For i = 0 To UBound(Ary2)
            .Remove (Ary2(i))
         Next i
         Range("P2", Range("P" & Rows.Count).End(xlUp)).AutoFilter 1, .Keys, xlFilterValues
      End With
   End If
End Sub