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.

 

[Legacy 102432]

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

Not quite..

How it works is this:

• A user will select an option from a validation drop down menu.
• If the value in the drop down menu is "" (I.e. if the user deletes the default "Select from list" value) then the range D2:D55 will prompt to "Please select a business unit".
• When a selection has been made, it performs the VLOOKUP which populates D2:D55 with values
• Not all of my table has values so some cells in D2:D55 will be blank, these rows I do not need, thus wanting to hide them!

Hope this gives enough info

 

[sous2817]

Sorry for all the questions...

So when there is no value in the VLOOKUP table, what value is in the D range? Is it a 0 (zero), or is it a blank cell?

Does adjusting the back half of your formula to:

IF(VLOOKUP(BusinessUnit,AppsTable,2,FALSE)="","",VLOOKUP(BusinessUnit,AppsTable,2,FALSE))

make a difference?

 

[Legacy 102432]

No worries at all

So when there is no value in the VLOOKUP table, what value is in the D range? Is it a 0 (zero), or is it a blank cell?

It would usually return a 0 but I did Tools > Options > Unticked Zero Values as I did not want the user to see "0" in every cell that wasn't in use.

Now I am developing my form I do not want numerous blank rows so I am trying to hide them altogether.

 

[Legacy 102432]

Does adjusting the back half of your formula to:

IF(VLOOKUP(BusinessUnit,AppsTable,2,FALSE)="","",VLOOKUP(BusinessUnit,AppsTable,2,FALSE))

make a difference?

I just tested the adjustment of the formula and it now works perfect. The rows are hiding upon having the value "".

Thanks very much for all your help

Just out of curiousity though, any ideas why it wouldn't work before hand with my original formula??

 

[sous2817]

I'm not entirely sure, but I think unticking that box just hides the value from view, it doesn't really make that value "", if that makes any sense.

Just adjusting the code to

    If rCell = 0 Then

or

    If Range("D" & i).Value = 0 Then

depending on the version you're using. Or you can adjust your formula to return a "" instead of a 0 (as in the example in my last post).

You may have to put quotes around the 0, I can never remember...

    If rCell = "0" Then

or

    If Range("D" & i).Value = "0" Then

Does that get any closer?

 

[Legacy 102432]

I'm not entirely sure, but I think unticking that box just hides the value from view, it doesn't really make that value "", if that makes any sense.

Just adjusting the code to

    If rCell = 0 Then

or

    If Range("D" & i).Value = 0 Then

depending on the version you're using. Or you can adjust your formula to return a "" instead of a 0 (as in the example in my last post).

You may have to put quotes around the 0, I can never remember...

    If rCell = "0" Then

or

    If Range("D" & i).Value = "0" Then

Does that get any closer?

Ah, I believe you are correct. It appears the zero values are still in place but obviously just hidden and shown as being "".

I have adjusted my code as follows as this is quicker than changing all my formulas in the range D2:D55 and it works! Great work.

Private Sub Worksheet_Calculate()
With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With
Dim rCell As Range
For Each rCell In Range("D2:D55")
    If rCell = "0" Then
        rCell.EntireRow.Hidden = True
    Else
        rCell.EntireRow.Hidden = False
    End If
Next rCell
Application.EnableEvents = True
End Sub

 

[sous2817]

Wonderful...glad it worked out! Best of luck with the rest of your project!

 

[Legacy 102432]

Much appreciated, thanks again

 

[Legacy 102432]

I have encountered an issue that you might be able to help me with...

I'll try to explain:

My form has 3 "sections" lets call them, as it needs to be forwarded to 3 different people so each can fill in their section. Only section one is showing upon the first person opening the form. Once their section is complete, they press a "Click here" button to send the form onto the next person.

Included within the first macro is a command to unhide the next section of the form, and lock the previous section that was just filled in.

I am having difficulties due to my most recent Worksheet_Calculate macro.

Because I have a drop-down in section 1, this changes values in section 2, but unhides the rows at the wrong time. I need the macro to ideally work in the background, and then only after pressing the "Click here" macro, will section 2 unfold.

Does that make sense? Sorry it's so long but I wanted to give full details of how it works to reduce any confusion and hopefully answer any questions in advance!

Any help is appreciated!

Thanks !

 

[sous2817]

Instead of having the code that hides the rows run when the worksheet calculates, you want it to just run when you press the button?

Put this at the top of your button code:

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With
Dim rCell As Range

and then:

For Each rCell In Range("D2:D55")
    If rCell = "0" Then
        rCell.EntireRow.Hidden = True
    Else
        rCell.EntireRow.Hidden = False
    End If
Next rCell
Application.EnableEvents = True
End Sub

at the bottom.