VBA Code for Auto Hide/Unhide Rows Based on above Row

[Ken0356]

Looking for VBA code to insert into my log workbook. I want a specific range of rows to be auto hidden until the row above it has value. This needs to repeat each time, so an example I want row 25 to appear once 24 is filled in, and the 26 to appear once 25 is filled in, and so on... Basically my log well be linked to hidden worksheets (field reports), that have certain cells that are linked back to the log report page. I want the the user not to see all of the unfilled in reports until they start progressing that far through the project, thus making it more printer friendly. In the end I'm got to have the first 25 rows/field reports visible on the log page and once they get past 25 and additional row will be come visible so they can continue filling in reports. Note in the end there will be 100 field reports that will be in this workbook. Hopefully this makes sense.

 

[Joe4]

Welcome to the Board!

I want a specific range of rows to be auto hidden until the row above it has value. This needs to repeat each time, so an example I want row 25 to appear once 24 is filled in, and the 26 to appear once 25 is filled in, and so on...

So, if row 24 is the last populated row, then you want the blank row 25 shown, but row 26 and below hidden?
How far down are we going in hiding (or are we hiding all the rows down to the bottom of the page)?
What column should we look at to determine if a row is populated or not (the update of which column should signal the code to run and expose the next row)?

I want the the user not to see all of the unfilled in reports until they start progressing that far through the project, thus making it more printer friendly

Is this done just for the purposes of printing?
If so, wouldn't it just be better to automatically run some "BeforePrint" code to only print down to the last row of data?

 

[Ken0356]

Joe4 - Thank you so much for the response.

So, if row 24 is the last populated row, then you want the blank row 25 shown, but row 26 and below hidden? Correct
How far down are we going in hiding (or are we hiding all the rows down to the bottom of the page)? Hiding rows say 12 through 112. There is printable reporting data after that so I don't think the "BeforePrint" would work unless we can 1. have it only print the first select constant rows, 2. then on specific rows and only if they have data, 3. and then print constant rows on the bottom
What column should we look at to determine if a row is populated or not (the update of which column should signal the code to run and expose the next row)? B

Again thanks a lot for the help, its really appreciated.

 

[Joe4]

OK, assuming the rows are hidden to start, this will unhide the row below the current row when column B is updated with a value.
To make sure you put this code in the correct place, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste the following code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
   
'   Check to see if update is to column B with a value
    If (Target.Column = 2) And (Target <> "") Then
'       Unhide the next row
        Rows(Target.Row + 1).EntireRow.Hidden = False
    End If

End Sub

This code will run automatically when column B is updated.

 

[Ken0356]

That works. Thank you so much

 

[Joe4]

You are welcome.

 

[Ken0356]

Joe4,
Sorry for another late question but I’m curious can the code be adjusted to allow a formula there and then update if there is an actual value? This code is being used on a log page in which column B is linked to a specific cell on each respective sheet (150 sheets link back to the main log) So when my users fill out each report it’s linked back to the main log. Right now I have my cell reference on the log in column B and the unhide feature no longer works as there is a formula there.

 

[Joe4]

What does the formula look like?

Can you give examples of what it returns when you do and when you don't want the row below hidden?

 

[Ken0356]

It simply returns the values from its related sheet. So it maybe easier to say greater than 0 (>0). Depending on the project though it could have a alphabetical prefix before the number
Two examples
V001
Or
001

 

[Ken0356]

It is empty by the way until they fill in the report so it only contains the formula and is blank until they fill in the respective report cell.