• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to hide range of rows, based on a cell having content

ursusblue

New Member
In the spread sheet (DATA), rows contain site information and calculations in the corresponding columns.


Trying to create an OUTPUT worksheet review of all the features and costs selected.


Column B is where it all starts with a site name.


What I want to do is if Column B, Cell 2 (B2) has content, make Rows 5-10 visible on the OUTPUT worksheet, if cell B3 has content, make rows 11-16 visible on the OUTPUT worksheet.


I am assuming the default would be all "content" rows on OUTPUT would be hidden in the beginning, then unhidden if DATA!B?? is populated


Possible? If so how?
 
Hi ,


Can you confirm the following ?


1. By default , all rows of the tab labelled "OUTPUT" are hidden.


2. Looking at the individual cells in column B on the tab labelled "DATA" ,


a) if B2 is not blank , rows 5 to 10 on OUTPUT will be unhidden

b) if B3 is not blank , rows 11 to 16 on OUTPUT will be unhidden


3. The above rule will be used for every subsequent cell in column B in DATA , till a certain end cell say B34.


4. What happens if any particular cell , say B17 is blank ? Are the corresponding 6 rows in OUTPUT skipped , and not unhidden ?


Narayan
 
Thanks for responding


1. Yes, all rows would be hidden

2. Yes.


3. Correct, it will be for 24 iterations, can't say if it would actually start at B3 or B9, (spreadsheet is geographically displaced from me at the moment), but it will continue for 24 rows.


4. If B17 is blank yes, those corresponding rows in OUTPUT would remain unhidden.


I'm hoping the end user wouldn't populate B16, skip B17, then populate B18, but I suppose it could happen.
 
Hi ,


Can you try out this code in the ThisWorkBook section of your project ?

[pre]
Code:
Public Sub unhide_rows()
Const ROWS_TO_UNHIDE = 4                       ' Change this to whatever number is required
Set ws1 = ThisWorkbook.Worksheets("DATA")      ' Change this sheet name as required
Set range_to_be_checked = ws1.Range("B5:B20")  ' Change this range address as required
Set ws2 = ThisWorkbook.Worksheets("OUTPUT")    ' Change this sheet name as required
Set range_to_be_unhidden = ws2.Range("A5:A80") ' Change this range address as required
ws2.Activate
number_of_rows = range_to_be_unhidden.Rows.Count
range_to_be_unhidden.EntireRow.Hidden = True
Set first_cell = range_to_be_unhidden.Cells(1, 1)
counter = 0
ws1.Activate
range_to_be_checked.Select
For Each cell In Selection
If cell <> "" Then
ws2.Activate
For i = counter To counter + ROWS_TO_UNHIDE - 1
If i <= number_of_rows Then
first_cell.Offset(i, 0).EntireRow.Hidden = False
End If
Next
End If
counter = counter + ROWS_TO_UNHIDE
Next

Set ws1 = Nothing
Set ws2 = Nothing
Set range_to_be_checked = Nothing
Set range_to_be_unhidden = Nothing
Set first_cell = Nothing
End Sub
[/pre]

Narayan
 
Hi, ursusblue!

As far as I can see the procedure name is "unhide_rows" (unquoted). Even if it is in the ThisWorkbook section, it won't run automagically unless it's called form another procedure, or in order to be run auto it'd be included within a "Private Sub Auto_Open()" in same section.

Regards!
 
Back
Top