Macro to hide/unhide rows based on cell value

[PuntingJawa]

I'm looking for a macro to hide and unhide rows in two specific ranges. If the value in each of the following ranges is blank, I would like it to hide that whole row. I've seen several macro's function automatically, but I plan to use a button. I've tried several variations, but they have not functioned well.
C21:C55 and C60:C94

 

[Joe4]

Are you saying that you want to hide rows 21:55 if ALL the cells in C21:C55 are ALL blank?
Or are you saying that you want to hide each of those rows individually if column C of that particular row is blank?

 

[PuntingJawa]

Are you saying that you want to hide rows 21:55 if ALL the cells in C21:C55 are ALL blank?
Or are you saying that you want to hide each of those rows individually if column C of that particular row is blank?

Each row individually. Sorry, I am having issues explaining myself well today.

 

[Joe4]

Try this:

Sub MyHideRows()

    Dim r As Long

    Application.ScreenUpdating = False

'   Check rows 21:55
    For r = 21 To 55
        Rows(r).EntireRow.Hidden = (Cells(r, "C") = "")
    Next r
    
'   Check rows 60:94
    For r = 60 To 94
        Rows(r).EntireRow.Hidden = (Cells(r, "C") = "")
    Next r
    
    Application.ScreenUpdating = True

End Sub

 

[PuntingJawa]

Try this:

Sub MyHideRows()

    Dim r As Long

    Application.ScreenUpdating = False

'   Check rows 21:55
    For r = 21 To 55
        Rows(r).EntireRow.Hidden = (Cells(r, "C") = "")
    Next r
   
'   Check rows 60:94
    For r = 60 To 94
        Rows(r).EntireRow.Hidden = (Cells(r, "C") = "")
    Next r
   
    Application.ScreenUpdating = True

End Sub

Perfect! Thank you as always!

 

[Joe4]

You are welcome.
Glad I was able to help!

Note the little trick/hack I did to shorten the code. Instead of doing an IF/THEN statement telling it to hide if column C was blank, or unhide if it was not, I used this statement:

Cells(r, "C") = ""

which will return TRUE if column C is blank and FALSE if it is not.

Then I used that on the Hidden property for that row, which tells it to hide it or not.

 

[PuntingJawa]

You are welcome.
Glad I was able to help!

Note the little trick/hack I did to shorten the code. Instead of doing an IF/THEN statement telling it to hide if column C was blank, or unhide if it was not, I used this statement:

Cells(r, "C") = ""

which will return TRUE if column C is blank and FALSE if it is not.

Then I used that on the Hidden property for that row, which tells it to hide it or not.

I like that trick. I will definitely have to keep it in mind. When I get home I will be updating the load planner I have been working on that this forum has helped me with on the thread I started. I added a bunch of functions to it so maybe other shippers can make use of it.

 

[PuntingJawa]

You are welcome.
Glad I was able to help!

Note the little trick/hack I did to shorten the code. Instead of doing an IF/THEN statement telling it to hide if column C was blank, or unhide if it was not, I used this statement:

Cells(r, "C") = ""

which will return TRUE if column C is blank and FALSE if it is not.

Then I used that on the Hidden property for that row, which tells it to hide it or not.

I just had this idea this morning. Is there a way to make this activate when a dropdown has been used as the event? I've been using buttons as the macro to be safe, but if I could make this automatically update with the dropdown, it would remove the need to have to press it and I could further automate. To give you an idea of the project I have been working on and improving, Truck load capacity I had updated my post on this board with all implementations that I have learned here. I do believe that I have all calculations down, but it was a massive amount of information given that I am working with volumes. I think I have been working on this for the greater part of a year.

Again, I appreciate everything this board has taught me.

 

[Joe4]

I don't think that there is any event tied to a drop-down, but if the drop-down is updating a cell, you could use a Worksheet_Change event procedure on that cell to trigger the code to run.

I just helped someone do that yesterday. See here: Make Message Box Only Appear Once

 

[PuntingJawa]

I'm trying to figure out how to link it all to changes in cell R18. The below is what you gave me for hiding. To link it all together wouldn't I have to remove the sub myhiderows() part and add it in because End Sub closes the loop? I'm just not quite sure the whole functionality behind it all. I do like how you've given me annotations to decipher what each part does. That's just great coding habit. But combining the two is where I am falling short.
What you've given me.

Sub MyHideRows()

    Dim r As Long

    Application.ScreenUpdating = False

'   Check rows 21:55
    For r = 21 To 55
        Rows(r).EntireRow.Hidden = (Cells(r, "C") = "")
    Next r
    
'   Check rows 60:94
    For r = 60 To 94
        Rows(r).EntireRow.Hidden = (Cells(r, "C") = "")
    Next r
    
    Application.ScreenUpdating = True

End Sub

What you've given them.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

'   See if an update is made to cell B2 or B3
    Set rng = Intersect(Target, Range("B2:B3"))

'   If not, exit sub
    If rng Is Nothing Then Exit Sub
    
'   Check to see if value in B4 is greater than 10
    If Range("B4").Value > 10 Then
        MsgBox "VBA code fired!"
    End If

End Sub

How I am deciphering the two together, but doesn't look correct to me as I mentioned, I feel like adding two Subs together wouldn't work correctly. Am I right in this way of thinking?

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

'   See if an update is made to cell R18
    Set rng = Intersect(Target, Range("R18"))

'   If not, exit sub
    If rng Is Nothing Then Exit Sub

Sub MyHideRows()

    Dim r As Long

    Application.ScreenUpdating = False

'   Check rows 21:55
    For r = 21 To 55
        Rows(r).EntireRow.Hidden = (Cells(r, "C") = "")
    Next r
    
'   Check rows 60:94
    For r = 60 To 94
        Rows(r).EntireRow.Hidden = (Cells(r, "C") = "")
    Next r
    
    Application.ScreenUpdating = True

End Sub