VBA Code for unhiding a specific # of rows

[lalahaedong]

I have a formula in cell A2 that provides a count from another worksheet. I would like to automatically unhide the number of rows from the count. The code that I wrote only unhides after selecting the cell and hit enter. What am I doing wrong, why isn't my code working?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$2" Then Exit Sub
Application.EnableEvents = False
Range("a6:A35").EntireRow.Hidden = True
Range(Range("A6"), Range("A6").Offset(Range("A2").Value - 1, 0)).EntireRow.Hidden = False
Application.EnableEvents = False
End Sub

MOD EDIT: Added Code Tags. Please use them when posting code snippets!

 

[Marc L]

According to forum rules edit your post and use the code tags via this icon :​

​

​

Adding an attachment may help, a lot …​

​

As your code is an event firing only when the cell A2 is changed manually so nothing weird …​

​

 

[BobBridges]

Lalahaedong, I gather you're typing a new value into A2 and hitting <Enter>, and then the Event program works. Right? Isn't that what you expected? What did you want to happen?

 

[Marc L]

As written « formula » so this event can't manage it …​

​

 

[lalahaedong]

I have 2 tabs. I will enter a value on the Customer Tab and the Name is linked to Address Tab. But, if I don't enter a name on the Customer Tab then I want to hide the rows. I would like them to unhide based on information entered on the Customer Tab. I thought the following code would work but it only works if I click on the name on Address Tab and hit enter?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$2" Then Exit Sub
Application.EnableEvents = False
Range("a6:A35").EntireRow.Hidden = True
Range(Range("A6"), Range("A6").Offset(Range("A2").Value - 1, 0)).EntireRow.Hidden = False
Application.EnableEvents = False
End Sub

 

[BobBridges]

I need a few more words in those sentences to be sure I understand what you're saying.

1) You enter a value on the Customer Tab—in what cell(s)?

2) "The name", you say, "is linked": What name? Do you mean that the value you enter on the Customer tab is a name, and that name "is linked"? Or is it some other name? And if it's some other name, where is it?

3) How is this name linked? Do you mean the new name is the same as a name on the Address tab, or do you mean that the user creates a hyperlink from the new value on the Customer tab to a cell on the Address tab, or what?

4) You say you want {the program} to hide "the rows": That can be made to happen, but which rows, exactly, and in which worksheet?

5) You say you would like to hide the rows "if I don't enter a name on the Customer tab". Do you mean you want those rows to be hidden until you enter a name on the Customer tab? Because you won't get Excel to do something by you not doing something—I mean, if you just sit there and stare at the worksheet, "not doing anything", Excel won't do anything either. I'm being silly, of course, but I figure you meant something else—and for me to understand what you meant, I need to get you to spell it out exactly.

6) Finally, you say you thought the code would work, but "it only works if I click on the name in the Address tab and hit <Enter>". I'll ask this one again: When did you want it to work?

Or let me ask it this way: Where did you place that code? Because that code should execute whenever a user changes something in a particular worksheet (the one belonging to the module where you placed that code), and the change takes place only when the user types something into a cell and hits <Enter>—which is just what you're saying is happening. If that puzzles you, I probably haven't understood yet what you expected, and thus I can't tell you how to fix it.