Hide Rows based on cell value being empty

[Legacy 102432]

Hi,

I have read other posts about this but I am still a little confused as most macros that have been made are tweaked to suite the users individual needs.

What I want is something like this to work in the active sheet:

If cells D2:D55 = ""
Then Hide.EntireRow

If cells D2:D55 = "has any value"
Then Show.EntireRow

The values in D2:D55 are populated by a VLOOKUP depending on what someone chooses in a drop down validation list, however not all the rows are always required so I would like to hide them to save some space on my form.

 

[sous2817]

See if this gets you close (of course there may be a better way):

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Single

For i = 2 To 55
    If Range("D" & i).Value = "" Then
        Rows(i).EntireRow.Hidden = True
    Else: Rows(i).EntireRow.Hidden = False
    End If
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

 

[Jon von der Heyden]

Hello Katie

Attach this to the relevant sheet:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br><br><SPAN style="color:#00007F">With</SPAN> Application<br>    .EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> rCell <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rCell <SPAN style="color:#00007F">In</SPAN> Range("D2:D55")<br>    <SPAN style="color:#00007F">If</SPAN> rCell = "" <SPAN style="color:#00007F">Then</SPAN><br>        rCell.EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        rCell.EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> rCell<br><br>Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[sous2817]

Hello Jon,

I'm just curious, is there a benefit to looping through the range using the method you laid out as opposed to the way that I did it?

 

[Legacy 102432]

Hi,

Thanks for your replies. I have used Jons example as I have better understanding of how it works.

It doesn't seem to be hiding the rows...Does this still work if there is a formula working in my range of cells to give a result of ""?

 

[Legacy 102432]

If i were to use your example, where would I paste the macro? Module/Sheet?

Could you also tell me what "i" represents? (sorry, still teaching myself VB! )

Many thanks

See if this gets you close (of course there may be a better way):

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Single
 
For i = 2 To 55
    If Range("D" & i).Value = "" Then
        Rows(i).EntireRow.Hidden = True
    Else: Rows(i).EntireRow.Hidden = False
    End If
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

 

[sous2817]

You'd use the same trigger as w/ Jon's code and paste it under the sheet you'd like the code to run:

Private Sub Worksheet_Calculate()

The i is just a number, in this case between 2 and 55. It starts as 2 and each loop increases by 1 until it gets to 55

So all it's doing is updating the reference range. It starts at D2, checks to see if it's blank. If it is, it hides the row (2), it it isn't it unhides the row. Then it ups the value of i to 3 and repeats.

From a test I've slapped together, it should work if your VLOOPUP returns a "" (no value).

 

[Jon von der Heyden]

Hi,

Thanks for your replies. I have used Jons example as I have better understanding of how it works.

It doesn't seem to be hiding the rows...Does this still work if there is a formula working in my range of cells to give a result of ""?

Hi Katie

Works for me

Are you sure you have attached it to the correct sheet? And are you sure your formula return ""?

It is prompted by calculation. So if you are on manual calc then you have to hit F9 for it to work.

 

[Legacy 102432]

Hi Jon,

Yes I have, I only have one worksheet.

My formula is currently this:

 =IF(ISBLANK(BusinessUnit),"Please select a business unit",VLOOKUP(BusinessUnit,AppsTable,2,FALSE))

...and thanks for the explanation sous2817

The i is just a number, in this case between 2 and 55. It starts as 2 and each loop increases by 1 until it gets to 55

So all it's doing is updating the reference range. It starts at D2, checks to see if it's blank. If it is, it hides the row (2), it it isn't it unhides the row. Then it ups the value of i to 3 and repeats.

From a test I've slapped together, it should work if your VLOOPUP returns a "" (no value).

 

[sous2817]

So in D2:D55 if the forumla returns a blank, it shows "Please select a business unit" (without the quotes). Is that correcT?