Hide and unhide rows by selecting a letter from a Combobox

[CBS]

Hide and unhide rows by selecting a letter from a userform ComboBox

--------------------------------------------------------------------------------

Hi, I have tried for ages .. but cant get it working..

i have a combobox and have A, B, C, as options in the combobox

then I have a table from row 10 to row 50

if A is selected from the combobox, I want excel to show only rows 10 to 20 and hide rows 20 to 50

if b is selected from the combobox, I want excel to show only rows 20 to 30 and hide rows 10 to 20 & 30 to 40

if c is selected from the combobox, I want excel to show only rows 30 to 40 and hide rows 10 to 30 & 40 to 50


in other words, i want all rows 10 to 50 hidden at all times, until a selection A, B, C is made in which i want thoes related rows to be shows and all other rows between rows 10 to 50 hidden...

Could someone please help me

thankyou

 

[acw]

Hi

Combobox from the control toolbox and on the relevant sheet.

Private Sub ComboBox1_Change()
  Rows("10:50").Hidden = True
  Select Case ComboBox1.Value
    Case "A"
      Rows("10:19").Hidden = False
    Case "B"
      Rows("20:29").Hidden = False
    Case "C"
      Rows("30:39").Hidden = False
  End Select
End Sub

You seem to be a bit off in your row nominations as you want certain rows both hidden and unhidden at the same time. B show 30, but hide 30...


HTH

Tony

 

[CBS]

Excellent..... that works..

now taking this a teeny step further...

This only works on the sheet that has the combobox in (say sheet1)... which is great

I would also like rows 10:50 to be hidden on sheet2 by default, BUT if I say select A from this combobox on sheet1, It will unhide rows 10:19 on sheet2..

if I say select B from this combobox on sheet1, It will unhide rows 20:35 on sheet2..

if I say select C from this combobox on sheet1, It will unhide rows 35:49 on sheet2..



how do i make this code hide/unhide rows on another sheet automatically ??

thankyou heaps

 

[acw]

Hi

Assuming that the combobox is on sheet1, then put
sheets("sheet2")
before the rows....


Tony

 

[CBS]

Hi mate, This combo box is on sheet1. When A is selected, I want certain rows in sheet1 hidden and certain rows in sheet2 hidden... will this do the job??


Private Sub ComboBox1_Change()
Rows("10:50").Hidden = True
sheets("sheet2").rows("10:50").Hidden = True


Select Case ComboBox1.Value
Case "A"
Rows("10:19").Hidden = False
sheets("sheet2").rows("10:19").Hidden = False

Case "B"
Rows("20:29").Hidden = False
sheets("sheet2").rows("20:29").Hidden = False

Case "C"
Rows("30:39").Hidden = False
sheets("sheet2").rows("30:39").Hidden = False


End Select
End Sub

 

[jindon]

try

Private Sub ComboBox1_Change() 
  Dim myRows As String

  Rows("10:50").Hidden = True 
  sheets("sheet2").rows("10:50").Hidden = True 

  Select Case ComboBox1.Value 
    Case "A" : myRows = "10:19"
    Case "B" : myRows = "20:29"
    Case "C" : myRows = "30:39"
  End Select 
  If Len(myRows) Then
     Rows(myRows).Hidden = True
     Sheets("Sheet2").Rows(myRows).Hidden = True
 End If
End Sub

 

[CBS]

Hi, thankyou...


When A is selected from the combobox in sheet1 I want to:

Hide all rows in sheet1 from 10:50 EXCEPT for rows 10:20

AS WELL AS

Hide all rows in sheet2 from 100:160 EXCEPT for rows 100:110


When B is selected from the combobox in sheet1 I want to:

Hide all rows in sheet1 from 10:50 EXCEPT for rows 20:29

AS WELL AS

Hide all rows in sheet2 from 100:160 EXCEPT for rows 110:120


When C is selected from the combobox in sheet1 I want to:

Hide all rows in sheet1 from 10:50 EXCEPT for rows 30:39

AS WELL AS

Hide all rows in sheet2 from 100:160 EXCEPT for rows 120:129



etc... this is the actual real example of hiding different rows from a drop down selection on a particular sheet.... sorry for my example earlier.. I wasnt clear... it is in actual fact hiding different rows in these sheets..

thankyou and I hope someone can shed some light..





and hide 100:200 in sheet2 and only show

Private Sub ComboBox1_Change()
Dim myRows As String

Rows("10:50").Hidden = True
sheets("sheet2").rows("10:50").Hidden = True

Select Case ComboBox1.Value
Case "A" : myRows = "10:19"
Case "B" : myRows = "20:29"
Case "C" : myRows = "30:39"
End Select
If Len(myRows) Then
Rows(myRows).Hidden = True
Sheets("Sheet2").Rows(myRows).Hidden = True
End If
End Sub

 

[CBS]

Hi, thankyou...


When A is selected from the combobox in sheet1 I want to:

Hide all rows in sheet1 from 10:50 EXCEPT for rows 10:20

AS WELL AS

Hide all rows in sheet2 from 100:160 EXCEPT for rows 100:110


When B is selected from the combobox in sheet1 I want to:

Hide all rows in sheet1 from 10:50 EXCEPT for rows 20:29

AS WELL AS

Hide all rows in sheet2 from 100:160 EXCEPT for rows 110:120


When C is selected from the combobox in sheet1 I want to:

Hide all rows in sheet1 from 10:50 EXCEPT for rows 30:39

AS WELL AS

Hide all rows in sheet2 from 100:160 EXCEPT for rows 120:129



etc... this is the actual real example of hiding different rows from a drop down selection on a particular sheet.... sorry for my example earlier.. I wasnt clear... it is in actual fact hiding different rows in these sheets..

thankyou and I hope someone can shed some light..

 

[jindon]

Private Sub ComboBox1_Change() 
  Dim myRows

  Rows("10:50").Hidden = True 
  sheets("sheet2").rows("100:150").Hidden = True 

  Select Case ComboBox1.Value 
    Case "A" : myRows =Array("10:19","100:110")
    Case "B" : myRows = Array("20:29","110:120")
    Case "C" : myRows = Array("30:39","120:129")
  End Select 
  If IsArray(myRows) Then
     Rows(myRows(0)).Hidden = True
     Sheets("Sheet2").Rows(myRows(1)).Hidden = True
 End If
End Sub

 

[CBS]

Hi there , i have adapted this to my workbook and I am gettin a:

runtime error '9'
subscript out of range

I cant figure what is wrong with this.... could you please help me out... thankyou heaps






Private Sub ComboBox1_Change()
Dim myRows

Rows("218:315").Hidden = True
Sheets("sheet2").Rows("2963:3357").Hidden = True

Select Case ComboBox1.Value
Case "A": myRows = Array("218:242", "2963:3063")
Case "B": myRows = Array("243:267", "3064:3162")
Case "C": myRows = Array("268:292", "3163:3261")
Case "D": myRows = Array("293:315", "3262:3356")
End Select
If IsArray(myRows) Then
Rows(myRows(0)).Hidden = True
Sheets("sheet2").Rows(myRows(1)).Hidden = True
End If
End Sub