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

Ken0356

New Member
Joined
Sep 30, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,393
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 30, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,393
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

New Member
Joined
Sep 30, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

That works. Thank you so much
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,393
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Ken0356

New Member
Joined
Sep 30, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,393
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 30, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 30, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,269
Messages
5,571,225
Members
412,372
Latest member
JON_ROCKS
Top