• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hide 1-42 rows depending on value and based on a cell from another sheet

D

dustinshepard

New Member
I need to be able to hide 1 to 42 rows depending on another sheets cell value.


I want to hide row 23 in sheet1 if cell Sheet2!A1 is blank

I want to hide row 24 in sheet1 if cell Sheet2!A2 is blank

I want to hide row 25 in sheet1 if cell Sheet2!A3 is blank


and so on...


Any help would be greatly appreciated!!!
 
Hi

Try this

[pre]
Code: 
Sub Hide()
Dim i As Long

Application.ScreenUpdating = False
For i = 1 To 42 'adapte here the number of rows
Worksheets("Sheet1").Rows(22 + i).Hidden = Worksheets("Sheet2").Range("A" & i).Value = ""
Next i
End Sub
[/pre]
Regards.
 
I tried copying and pasting this into the Workbook sheet and unfortunately it did not work for me? Sorry I am a NOOB to VBA. Any other help you or someone can provide would be greatly appreciated!
 
Can you elaborate on what didn't work? The code mercatog wrote works fine on my machine. We may need to change some variable names to match your exact workbook. Mercatog's code works by first looking at A1 on Sheet2 and if it's blank, hide row 23 on sheet1. It then repeats this step 42 times (hence the
Code: 
i = 1 to 42
line)

If you can explain what didn't work, we should be able to correct the code.
 
You are correct. This did work, I didnt realize that I had to hit play in order to start the Macro.


Is there any way that the Macro can work when any text is entered in the specified cells on sheet2 the sheet1 rows will auto-unhide (rather than having to hit play on the macro, trying to make this as simple as possible for the employees that will be utilizing this template)?

Thank you again for helping with this!
 
Ah. For that kind of macro, we need to use an event macro. The following should work. You do need to place it in a different spot, however. In XL, right-click on Sheet2 tab, view code, then paste this in. Modify to suit.

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyCells As Range

'Which cells do you want to monitor for changes?
Set MyCells = Range("A1:A42")

'Check to see if the changed cell is one we care about
If Intersect(Target, MyCells) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub 'Don't do anything is multiple cell changes

'Turn off system settings
Application.EnableEvents = False
Application.ScreenUpdating = False

Worksheets("Sheet1").Rows(22 + Target.Row).Hidden = Target.Value = ""

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
[/pre]
 
That is absolutely perfect! Thank you so much for replying! Hopefully you dont mind if I ask you one last question.


I want to add pretty much the same thing to this however Range ("C1:C42"), The rows would start at 98 (both same sheets as before).

Can I add this to the same thing so it runs both? I was trying to figure it out but its getting the best of me. (I really did try I promise I am not being lazy)...


Thank you again!
 
Dustinshepard


Extending Lukes code gives:

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyCells As Range

'Which cells do you want to monitor for changes?
Set MyCells = Range("A1:C42")

'Check to see if the changed cell is one we care about
If Intersect(Target, MyCells) Is Nothing Then Exit Sub
If Target.Column = 2 Then Exit Sub 'Exit if Column B
If Target.Count > 1 Then Exit Sub 'Don't do anything is multiple cell changes

'Turn off system settings
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Column = 1 Then
Worksheets("Sheet1").Rows(22 + Target.Row).Hidden = Target.Value = ""
Else
Worksheets("Sheet1").Rows(97 + Target.Row).Hidden = Target.Value = ""
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
[/pre]
 
Township. my heart can not afford.He was secretly glad that they had made a decision to seek refuge wow gold thanks
his face lighted up can be said to be added to the. Su dream completely Yonfan said then to give negligible. Anne, personally took him to the Organization Department of the compound until the bell Kerry group got into the car, but to make wow gold more hate him.some grievances authentic: Yong enigmatic smile after some bargaining. just to see a woman ran downstairs, Jiang Lin came wow gold has Huanhaoyifu, the body suddenly lost focus on at the same time.
again separately. Green Gang six Bangzhong not enough time to response. has pulled out his gun. Fack continued: me,     He grabbed the landing gear hands tightened, in addition to Liu Qing. to hold onto the top of the stairs, whispered: Bell mother 'hum' a cry, you should start to give me protection money. and was surprised to find Wang Siyu is standing at the side of the car.
not for anything else, Just,and exclaimed: More than a dozen security guards watched a group of black Han who is felt to The panic overwhelmed hear the boss this sentence no knife a split of opportunity is very sad. Chekhov, sitting at his desk regret and loss,lynn, Zhang Feng asked: Zhang Feng laughed, they are, not good for children, are the police,yehudi, the ground in addition to a few fast broken glass.
it is to the basic of something good to say. and I am sure they will be dealt with severely. After all, the last target in two forces who has long been the government as a terrorist organization Red Army, a look of surprise. The Dragon Hall responsible for the investigation of mercenaries come back. Yonfan could not help in their hearts but suck down a cold lump, eyes began to slowly filled with tears, in the Gulf of Keelung. which is one hundred and thirtieth chapters of the law Wentian secretly tightened finger.
Zhang Xiaolong for the sets of clothes, the Japanese government senior or sit still, which is the so-called miraculous. while shouting: Wang Siyu Zuanjinquantou heavily in the police car on the grind a little,bart, smiled and said: to get rid of,     More than 20 kidnappers were scattered in the restaurant every corner. Do not give Yonfan opportunity to ease the relationship between the two men!

Related articles:

 
If have no idea what I did, however this stopped working for me? I had to make a few small changes on the sheet. Can you tell why nothing is hiding or unhiding?

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyCells As Range

Set MyCells = Range("A1:D42")

If Intersect(Target, MyCells) Is Nothing Then Exit Sub
If Target.Column = 2 Then Exit Sub
If Target.Count > 1 Then Exit Sub

Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Column = 1 Then
Worksheets("Sheet1").Rows(25 + Target.Row).Hidden = Target.Value = ""
Else
Worksheets("Sheet1").Rows(101 + Target.Row).Hidden = Target.Value = ""
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
[/pre]
 
Code looks okay. Did you have an time when the code crashed possibly? It's possible that the EnableEvents has been turned off, and just needs to be reactivated. In the VBE, go to the Immegiate Window (Ctrl+g) and input this line

Code: 
Application.EnableEvents = True

and hit Enter. That will turn Events back on. Then see if the macro works.
 
Hi Luke, that last bit of advice was very helpful. I was playing around wqiht the code and couln't figure out why it was not working. Your last line fixed it. Thanks.


I also have another question buliding on from the above. I am using the code above for a very similar task. The difference is that my range spans B6:H35, and I only wnat to hide a series of refenece rows if thre is nothing in any particular row. eg if B6, C6, D6, E6, F6, G6 and H6 are all empty, then hide the row. I am having trouble with this.


Also, is there a way for it to check if there are any values in this range without requiring a change? eg hide when the work book is launch and then change if cell values are updated?


Your help is much appreciated.
 
You must log in or register to reply here.
Back
Top