Need code to unhide next row without data

**Mike Church** · 02-03-2012, 04:57 PM

I have a worksheet that will have a large number of preformatted rows but only want to show the first five or so and then have the others hidden. Once the user gets to say row 5 or more specifically when they enter data into B5, row 6 unhides. If the user enters information into B6 then row 7 unhides, etc.

**Paul** · 02-03-2012, 05:28 PM

Hi Mike, welcome to the forum.

Hopefully this code works for you. It not only un-hides the rows as you add data into column B, but it re-hides unused rows if you start deleting values and there is nothing left in column B below that point. If you clear multiple cells at once, leaving a number of blanks, you just have to pick one of the remaining blanks in column B and press Delete for them to become hidden.

To add this code to your workbook: Right-click on the sheet tab for which you want this code to be triggered and choose View Code. Copy the code below and paste it into the VB Editor window. Close the VB Editor window. From that point forward, any change you make to cells in the range B5:B1048576 will trigger the macro.

Please Login or Register  to view this content.

**Mike Church** · 02-03-2012, 05:51 PM

Thanks Paul this works exactly as I described the problem. After looking at what I have built, I left out an important part of the detail. I do have additional portions of the spreadsheet that occur in rows after row 50. Is there a way to cap the end of the range for the range references you provided. I tried to "drop in" the value of 50 where i thought appropriate, however I got a debug error. Thanks and sorry for the oversight.