Hiding rows issue

J

Jemmi

New Member
Joined
Jun 18, 2014
Messages
12
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:


Code: 
[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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Does col P only contain certain values & if so how many different values could there be?
 
Upvote 0
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.
Click to expand...
but the code is not looking at the values, it is hiding specific rows, irrespective of their values:
[TABLE="class: cms_table, width: 65"]
<tbody>[TR]
[TD] ElseIf target = 4 Then[/TD]
[/TR]
[TR]
[TD] ActiveSheet.Rows("15:18").EntireRow.Hidden = True[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
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?
 
Upvote 0
Fluff said:
Does col P only contain certain values & if so how many different values could there be?
Click to expand...

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?
 
Upvote 0
Joe4 said:
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?
Click to expand...

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.
 
Upvote 0
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.
 
Upvote 0
Joe4 said:
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.
Click to expand...

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:

Enter number herePath
AB1
AB2
QW1
QW2
QW3
QW4

<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>
[TD="width: 116"]COLUMN B[/TD]
[TD="width: 65"][/TD]
[TD="width: 65"][/TD]
[TD="width: 65"][/TD]
[TD="width: 72"]COLUMN P[/TD]

[TD="class: xl66"]2[/TD]

[TD="class: xl66"]1[/TD]

[TD="class: xl66"]AB1[/TD]

[TD="class: xl66"]AB2[/TD]

[TD="class: xl67"]<s>AB3</s>[/TD]

[TD="class: xl67"]<s>AB4</s>[/TD]

[TD="class: xl66"]QW1[/TD]

[TD="class: xl66"]QW2[/TD]

[TD="class: xl66"]QW3[/TD]

[TD="class: xl66"]QW4[/TD]

[TD="class: xl67"]<s>QW5</s>[/TD]

[TD="class: xl67"]<s>QW6</s>[/TD]

[TD="class: xl67"]<s>QW7</s>[/TD]

[TD="class: xl67"]<s>QW8</s>[/TD]

[TD="class: xl66"]2[/TD]

[TD="class: xl66"]AB1[/TD]

[TD="class: xl66"]AB2[/TD]

[TD="class: xl67"]<s>AB3</s>[/TD]

[TD="class: xl67"]<s>AB4</s>[/TD]

[TD="class: xl66"]QW1[/TD]

[TD="class: xl66"]QW2[/TD]

[TD="class: xl66"]QW3[/TD]

[TD="class: xl66"]QW4[/TD]

[TD="class: xl67"]<s>QW5</s>[/TD]

[TD="class: xl67"]<s>QW6</s>[/TD]

[TD="class: xl67"]<s>QW7</s>[/TD]

[TD="class: xl67"]<s>QW8</s>[/TD]

[TD="class: xl66"]3[/TD]

[TD="class: xl65"]<s>AB3</s>[/TD]

[TD="class: xl65"]<s>AB4</s>[/TD]

[TD="class: xl65"]<s>QW5</s>[/TD]

[TD="class: xl65"]<s>QW6</s>[/TD]

[TD="class: xl65"]<s>QW7</s>[/TD]

[TD="class: xl65"]<s>QW8</s>[/TD]

</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.
 
Upvote 0
Do any of your values go above 9, so could you have AB10 or QW10?
 
Upvote 0
In that case maybe something like
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Creating a 'Reset' button for a text form, plus additional buttons
Replies
2
Views
309
WEB78
W
R
  • Solved
Hide rows according to checkboxes on seperate sheet
Replies
8
Views
632
John_w
J
N
  • Question Question
Hiding/showing rows between two values
Replies
1
Views
480
Joe4
Joe4
I
  • Question Question
VBA to detect hidden rows. then Re-hide
Replies
7
Views
208
Skyybot
Skyybot
imback2nite
  • Solved
VBA help.
2
Replies
10
Views
803
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,171
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top