Hide/unhide variable number of rows

Hamza Oza

New Member
Joined
Nov 16, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I am trying to use a code that allows me to hide/unhide x number of rows based on another cell value. I also need this to update automatically when the cell value changes.

So for example if cell A1 is =1, I want it to show row 5 and hide rows 6-10, when the cell value changes to 2, I want it to show row 5-6 and hide rows 7-10, when the cell value changes to 3, I want it to sow rows 5-7 and hide rows 8-10 … and so forth.

I am very new to VBA coding, thus don't have a VBA I am using for the above. Any suggestions will be highly appreciated.
 

Hamza Oza

New Member
Joined
Nov 16, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
You can use one another cell for formula, e.g. D3.
Then just equate A1 with D3.

Hope it helps.
Hi. So lets say I am using a countif formula, are you saying I should try putting the countif in D3 and then in A1 put "=D3"? Because when I tried putting the countif in A1 it didn't work.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

LazyBug

Board Regular
Joined
Feb 28, 2020
Messages
156
Office Version
  1. 2010
Platform
  1. Windows
Hello!
Exactly! Put a formula to other cell (D3 in my example) and then put in A1 "=D3". It should work.
 

Hamza Oza

New Member
Joined
Nov 16, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hello!
Exactly! Put a formula to other cell (D3 in my example) and then put in A1 "=D3". It should work.
Hi, I tried it and unfortunately it does not work. It only works when I manually input the number into A1. I've been struggling with the VBA to make it update live. Not sure if there is any other solution?
 

Hamza Oza

New Member
Joined
Nov 16, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I think @alansidman's formula works. Have to manually input the hide/unhide 25 times because I actually have 25 line items of data but it updates when cell value changes.
Not sure if there is an easier way though
 

cafeannalisa

New Member
Joined
Apr 29, 2017
Messages
9
Office Version
  1. 2016
Platform
  1. MacOS
While this is 'old' i seem to have basically the same question as Hamza so will post here first just in case
Columns J1-N1 have headers 1 2 3 4 5
I want to hide columns based on S1, which takes values 1-5
If S1 = 1 Hide columns headed 2-5 i.e. K:N
If S1 = 2 Hide columns headed 3-5 i.e.L:N
If S1 = 3 Hide columns headed 4-5 i.e.M:N
If S1 = 4 Hide column headed 5 i.e. N
[If S1 = 5 No hiding]
I have tried a version of alansidman's code above without success. And apologies but don't understand LazyBug's (i.e how to adapt)

Private Sub Worksheet_Change(ByVal Target As Range)

Set Target = Range(“S1")
If Target = 1 Then
columns(“K:N”).Hidden = True
ElseIf Target = 2 Then
columns("K:N").Hidden = False
columns(“L:N").Hidden = True
ElseIf Target = 3 Then
columns("K:N").Hidden = False
columns(“M:N").Hidden = True
ElseIf Target = 4 Then
columns("K:N").Hidden = False
columns(“N”).Hidden = True
End If
End Sub
 

Attachments

  • Screenshot 2021-01-01 at 16.41.53.png
    Screenshot 2021-01-01 at 16.41.53.png
    85.1 KB · Views: 1

Watch MrExcel Video

Forum statistics

Threads
1,126,996
Messages
5,622,078
Members
415,875
Latest member
Tarali

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
Top