VBA to Automatically Hide Unused Rows

Question

What would it be, and what's the best way to go about this with VBA to automatically hide the unused rows as well as the entire section (i.e., 2 bed, 3 bed, studio, etc.) if the subject property doesn't have a rent figure entered? I don't want to have to click a button; it needs to be automatic (or dynamic?).

Worksheet_Calculate sometimes bogs down the template or the code gets interrupted if I have another complex workbook opened, so I'd rather it not be that, but it's fine if that's the only option.

What I originally planned was to have a hidden helper column (e.g., column z or something) and use VBA to have the value be there what the value is in, for example, B36. But I see now that that only worked with my data validation boxes in another report I use, and I still have the problem of VBA not hiding the row when the value rendered from a formula changes. Is Worksheet_Calculate the only option?

Note that the data changes (or the sections or break-outs populate) when the years and miles are adjusted. Also note that the lead column is Column B (the property names where the break-outs are).

Example file here.

Thank you in advance. (All credit to Lz on this forum for getting me this far!)

Answer 1

Good morning, Jeovany. Hope you are well. Just touching base with you and checking to see if you got my last response. Thank you.

Answer 2

Good morning, Jeovany. Thank you for this! Please pardon my delay. This is way outside of my current experience level.

The sample file you provided is great. I really don't know where to begin, to apply something like this to my main template with more details. I will have to learn this, though.

Some other details:

• The thresholds for miles and years would need to be in a separate area, as they are user boxes after the main data is entered. (In my main template I have those, plus a few others, between the comps input section and the output section. I moved them to the side just to simplify this public example.)

• The years threshold is to narrow comps between a certain age range. E.g., we may want to keep the comps to within 10 years newer than the subject property depending on the deal, or increase it to 20 years at times. Or maybe even just within 5 years on either side if the submarket only contains new product or properties all of the same vintage. (The report from which the data comes sometimes contains comps in the 1990s or 2000s when, for example, the subject property is from the 1960s or '70s.)

• I like your thinking behind adding conditional formatting to highlight rows. I had even set the font color to change in my main template.

• Since I'm designing a template to share with my group (not everyone is Excel-savvy!), I would prefer the 'refresh' to work automatically. I assume I could set up a Worksheet_Change to run the refresh when the data in, for example, K4 changes?

As to VBA, I'd still like to at least explore that route in order to compare the two options we've discussed. Do you mind helping with the hiding of the rows part? For hiding the sections if the subject property doesn't have that bedroom type entered, I've got this so far:

Private Sub Worksheet_Change(ByVal Target As Range)

     If Not Intersect(Range("G5"), Target) Is Nothing Then

         Range("34:73").EntireRow.Hidden = (Range("G5").Value = "")

     End If

     If Not Intersect(Range("H5"), Target) Is Nothing Then

         Range("74:113").EntireRow.Hidden = (Range("H5").Value = "")

     End If

     If Not Intersect(Range("I5"), Target) Is Nothing Then

         Range("114:153").EntireRow.Hidden = (Range("I5").Value = "")

     End If

End Sub

Thank you for any help.

Answer 3

Hi Ryan

Good morning!

Re, "...  I don't know that I'll have time to go learn Power Query just for this, but would Power Query function as simply with the needed changes that I've just described? "

Well... I'd said the time you need to learn Power Query just for this, is a lot less than the time you spent building/finding the formulas and named ranges plus the time invested in the forum looking for a solution to your template, That's for sure.

And YES, Power Query will provide you with the dynamic changes and automation you might require.

We just need to create the correct scenario/layout and parameters.

You may find in the link below your sample file with the Power Query solution as per my understanding of your requirements and scenario.

https://drive.google.com/file/d/1b6efmUf3J55He1O1W6FsPp8uAjVA_n6s/view?usp=sharing

NOTES:

In comparison with your previous file

1. This approach does not require formulas
2. Does not requires multiple named ranges
3. Does not requires separate cells for Miles and Years Threshold

BTW, I did not fully understand the logic behind the "Years thresholds" yet the results from the query are identical to the ones in your original example workbook.

Please, clarify about it.

The picture below shows the new template scenario

I also added a conditional formatting rule to highlight rows (Yellow) in the Comp table that match the threshold conditions (i.e. <=Threshold eg <=1.5 miles)

You need to enter the Miles threshold in cell K4 (in Yellow).

And go to the Data ribbon tab and refresh the query

RESULTS

In my next reply, I will give more details about the query process.

Now I'm very busy and I have a meeting to attend.

Play with the file and let me know how it works, about the Year thresholds, and any other doubts.

Regards

Jeovany

Answer 4

Good morning, Jeovany. Thank you! Pardon the confusion I may have created. There wouldn't be any hiding of columns, only the hiding of the unused rows in the break-out/sort sections (where the comps plus the subject property are sorted from high to low in red font) as well as the break-out/sort sections themselves for each unused bedroom type.

Would the Worksheet_Change Event just be for hiding the unused break-out/sort sections (since the subject property's rents are entered manually) while Worksheet_Calculate for the unused rows within those break-out/sort sections since those are generated from formulas?

Re: Power Query. lol—great analogy. Yes, that is a 100 mph curveball.

The template's "thresholds" would be being updated frequently and thus the data changing automatically, and the Example file I provided is a stripped-down version (I have other conditions on my other version). E.g., after the data is entered and depending on the subject property, there may be adjustments to the years, ranges/miles, etc., and the sorting updates each time one of those changes is made so that if not much is rendered from, for example, the 1.5 mi. threshold, I may type in 2 mi. instead depending on the area, and so forth. I don't know that I'll have time to go learn Power Query just for this, but would Power Query function as simply with the needed changes that I've just described?

Answer 5

Hi Ryan

Re:

"What would it be, and what's the best way to go about this with VBA to automatically hide the unused rows as well as the entire section (i.e., 2 bed, 3 bed, studio, etc.) if the subject property doesn't have a rent figure entered?..."

We would need to use the Worksheet_Change Event and a helper Sheet to hide the cells Unused Rows in your Tables

The problem would be to hide the Unused Columns (i.e., 2 bed, 3 bed, studio, etc.) in your tables.

In the picture below from your sample file, the "Studio" column yields a "Blank" value from the formulas in it

If I understood correctly you would like to hide that "Unused column". If we do so It would affect the Comps table and the Subject data entry table at the top of the template.

Let's assume the macro hides the unused rows and columns automatically as your requirement

How could we unhide back the columns?

As I see it

We would need to create 2 sets of data entry processes/orders for the ranges B5:D5 and E5:J5

Re:

"Worksheet_Calculate sometimes bogs down the template or the code gets interrupted if I have another complex workbook opened..."

If so...

IMHO, Power Query might provide you with the dynamic solution you are currently requiring.

I know this is a 100 mph curveball,

Especially when your template is very deep in amazing formulas and named ranges. And certainly, those who helped you to achieve that made their assumptions and found the solutions/formulas based on the details, data, and previous requirements.

But for the dynamic solution, you are now after, a complete overhaul in the structure of your tables might be needed.

Regards

Jeovany