VBA Code to hide rows if Cells = 0 or blank

[robertk93]

I am working on two different sheets within the same workbook. I have cell headers for certain sections- that when sub items have a value, the header auto fills, thus I need to use a formula similar to this above (I would guess). Any help is much appreciated!

I have one sheet where I input the value and if a certain line item has a number in it (not blank), then it would move over to the other sheet with the line item description, etc. I also have different sections that certain line items fall under.

 

[Joe4]

Welcome to the Board!

With these kind of questions, it is really hard to paint a clear picture of your data structure and your goal with just a short explanation. Seeing an example of your data and expected output would go a long way in showing us what exactly you are working with and trying to accomplish. So I recommend you post a small example of your data and your expected output.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[robertk93]

Joe4- thank you! I am happy to send you a copy of my sheet with an explantion or do a zoom call with you to explain.

Thanks!

 

[Joe4]

Joe4- thank you! I am happy to send you a copy of my sheet with an explantion or do a zoom call with you to explain.

Thanks!

That is not quite the way this board works - we request that we keep everything on the public forums.
See rule 4 here: Message Board Rules

 

[robertk93]

Joe4-

Apologies. I'll upload some images here with an explanation.

Image 1- This is a pricing proforma for services. I'd fill in a numeric value under activity and a price in the price column for each line item on the left if I was charging the customer for that service. The blue rows are the category headers. The customer name and balance computation for the period should move over automatically.

Image 2- The text in service description under each header, should move over to page 2 should a numeric value be input into the activity column. If any of the line items under the header have a value in activity (all or one), the header will then populate also.

If there isn't a value in any of the activity of a line item, the row on image 2 should hide and if a value is entered on page 1, it should unhide automatically.

Let me know if you need anymore information.

Thanks!

 

[Joe4]

Let's say that you wanted to return a value from cell B1 on Sheet1 to B1 on Sheet2, but only if it has a value (otherwise, leave blank).
Then you could use a formula like this in cell B1 on Sheet2:

=If(Sheet1!B1="","",Sheet1!B1)

 

[robertk93]

Joe4-

That is partly what i'm trying to accomplish. Let's say I have a value in B27 on page 1 but no value in B25 or B26, row 25 and row 26 on page 2 should hide, but A24 should display the header. The rows should hide if they have no value, but the header should display should one line item in the category have a value.

Thanks!

 

[Joe4]

Let's say you wanted to check each cell in range B25:B27 for a value and hide the row it if it is blank.
You could do something like this:

Dim cell as Range
For each cell in Range("B25:B27")
    If cell.Value="" Then
        Rows(cell.Row).Hidden = True
    Else
        Rows(cell.Row).Hidden = False
   End If
Next cell

 

[robertk93]

Joe4- Got it. Would I need to reference a specific sheet?

That worked! Question- how would I make this happen automatically without having to run the macro each time? Also, how could I do this for B35-B127? But there are other headers in between B35-B127.

I'd need to hide like you showed above, but if one of the items has a value- the header would show.

Thank you as you have truly helped!

 

[Joe4]

Would I need to reference a specific sheet?

It depends on where the VBA code is placed and when it is run.
If you leave sheet references off of your range, it will default to whatever the active sheet is at the time that it runs.
You can specify the sheet by either selecting it first, i.e.

Sheets("Sheet1").Activate

or by adding sheet references to your range, i.e.

Sheets("Sheet1").Range("B25:B27")

how would I make this happen automatically without having to run the macro each time?

It depends. Automatically based on what?
There is automated code in VBA called "Event Procedure" code, but it is dependent on some action happening, such as the update of a specific cell, the selection of a specific range, a calculation happening, a workbook opening or closing, etc.
Chip Pearson did quite a big write-up on them some years ago: Events In Excel VBA

Also, how could I do this for B35-B127? But there are other headers in between B35-B127.

If there is some way to identify/differentiate the headers from the other data, that could be incorporated in the code.
For example, if the headers were always exactly every 7 rows.
Or if they started with the same word.
Or maybe in your case, the cells have a different background color.

For example:

Dim cell As Range
For Each cell In Range("B35:B127")
'   Check to make sure cell is not colored in specific blue color
    If cell.Interior.Color <> 16737792 Then
'       Check to see if cell is empty/blank
        If cell.Value = "" Then
            Rows(cell.Row).Hidden = True
        Else
            Rows(cell.Row).Hidden = False
        End If
   End If
Next cell

Note that in order to get the color code for any cell, you can use a little procedure like this:

Sub GetColorCode()
    MsgBox Range("A11").Interior.Color
End Sub

Just change the "A11" to the address of the cell you want to get the color code for, then run this code, and the code will pop-up on the screen (and you can use that number in your code).