Forum Discussion
Hiding and automatically unhiding row
My worksheet has numerous rows that may not be used each particular time I open it. I want to be able to keep the descending rows hidden until text is inserted into the row above it. That is, say only row A6 is visible on opening the worksheet., and rows A7.A100 are hidden. When the user enters text in A6, only then will A7 automatically become unhidden, and so forth down the page. Is there a way to set this up, say a formula in conditional formatting?
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Long r = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Application.EnableEvents = False Cells(r + 1, 1).EntireRow.Hidden = False Range(Cells(r + 2, 1), Cells(Rows.Count, 1)).EntireRow.Hidden = True Application.EnableEvents = True End SubSwitch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open the workbook.
Warning: the code will disable Undo.
- I have the same issue as RichardJ0958 above. I have copied the code above into "View Code" in the worksheet module but it is not working for me. Can you help me find out what I am doing wrong?
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.
