Suppressing a row in which a zero appears

A sample file would always be the best. You can anonymize all the data, but try to keep all the situations possible in column P as closely as they are.
My approach would be VBA, as formulas can't really hide the whole row.

Hi, and thanks.
I attach a sample of the type of thing I want with this email. Where a number in Column P is zero, I want that row suppressed entirely. As you can see, sometimes Column P is merged into a description row.
I can't really attach the real spreadsheet, so if VBA is the solution, can you explain what you do so i can emulate this in the actual worksheet?
Thanks again.
Example-of-row-suppression-requirement.x

What should happen with these "empty" or "header" rows? Also hide (hope so, that's the easiest!)

No, sometimes the row headings will still be relevant to the stuff beneath, if one of the rows under that heading has a non-zero number in Column P. I definitely need the rows to be there, even if there is nothing in the rows beneath. Our system needs the row headers, as somebody has to positively check (manually) that the particular row heading has no relevant data beneath it. Sorry!

In your sample workbook the only place I see column P merged is in the TOTAL rows. Is it merged someplace else that I missed?

The total rows, yes - plus the headings, e.g. MEMBRANE ACCESSORIES, Doors, Window, and so on.

Try this. It removes rows that have 0 in P or P is blank.

Sub RemoveRows()
Dim lngLastRow As Long
Dim lngRow As Long

With Sheets("List of parts")
    lngLastRow = .Range("B1048576").End(xlUp).Row
    For lngRow = lngLastRow To 8 Step -1
        If .Cells(lngRow, "B") = "" Then
            If .Cells(lngRow, "P") = 0 Then
                .Cells(lngRow, "A").EntireRow.Delete
            End If
        End If
    Next
End With
End Sub

Select allOpen in new window

This just removes the zeros.

Sub RemoveRows()
Dim lngLastRow As Long
Dim lngRow As Long

With Sheets("List of parts")
    lngLastRow = .Range("B1048576").End(xlUp).Row
    For lngRow = lngLastRow To 8 Step -1
        If .Cells(lngRow, "B") = "" Then
            If .Cells(lngRow, "P") = 0 Then
                If Len(.Cells(lngRow, "P")) > 0 Then
                    .Cells(lngRow, "A").EntireRow.Delete
                End If
            End If
        End If
    Next
End With
End Sub

Select allOpen in new window

Do you want the row to be really deleted with no options to get it back? Then the code provided will probably work.
Otherwise, if you want it hidden (so you can get it back to original), the delete lines should be changed to hide lines.

To all,
No, I need the lines back.
Basically, I just want them suppressed, not removed, when Column P shows a zero.
Thanks for all your assistance - it's really good! BTW, my knowledge of VBA is minimal to non-existent; but I shall persevere. Maybe I should send the entire spreadsheet to you, but I don't really want it advertised to the world.

where you see something like:

.Cells(lngRow, "A").EntireRow.Delete

replace it with

.Cells(lngRow, "A").EntireRow.Hidden = True

After everything is done, you can get all the lines back manually by selecting a host of rows (have the hidden ones in the selection, obviously) and rightmouseclick Unhide.

My problem, I know, is that I am an absolute novice at VBA. If I sent the entire spreadsheet, maybe you could show me the code there. The trouble is that it's commercially sensitive (there are a few people who would find it useful, and they're competitors). I would like to analyze what you wrote for my made-up example sheet, but am incapable of adapting it to my real spreadsheet, at least, not unless I spend a month or two (or a year or two, more like) learning VBA. I don't really expect you to write my VBA code for me - why should you? - but I just don't know how to get any further.

I have extracted the (not yet complete) worksheet in question, but of course all the worksheets that feed into this I have not included. But maybe, now that you see this worksheet, you could advise on the methodology of suppressing the rows where Column P carries a zero? If I knew the logic, i could then probable extend the process when I have finally completed this worksheet (attached)
PAL-worksheet.xlsx

Every time someone posts VBA code here, just click the "select all" link under it, then copy it.
In excel, press ALT+F11, and in the workbook, paste it.
You can run the code with ALT+F8.
Whenever it's code you don't really understand, just use a copied Excel file (keep original and always back up)

Add the following two macros to your workbook. To do that...
In Excel, Press Alt+F11 to open the Visual Basic Editor (VBE)

Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu

Copy the macro (you can use the ‘Select All’ button if you like) and paste it into the right-hand pane of the VBA editor ("Module1" window)

Press Alt+F11 again to go back to Excel

Optionally, press Alt+F8 to open the "Macro" dialog window. Select the HideRows macro, click ‘Options…’,  hold down the Shift key and type the letter H (or any other letter) and click ‘OK’. Then anytime you want to run the macro press Ctrl+Shift+H. Do similar for the UnHideRows macro using Ctrl+Shift+U (or any other unused letter).

When you close the workbook you will need to save it as an xlsm or xls file if it’s not already one of those.

Your worksheet is password protected so you will need to change lines 9 and 19 to use your password rather than "MyPassword".

Option Explicit

Sub HideRows()
Dim lngLastRow As Long
Dim lngRow As Long

With ActiveSheet
    lngLastRow = .Range("B1048576").End(xlUp).Row
    .Unprotect Password:="MyPassword"
    For lngRow = lngLastRow To 8 Step -1
        If .Cells(lngRow, "B") = "" Then
            If .Cells(lngRow, "P") = 0 Then
                If Len(.Cells(lngRow, "P")) > 0 Then
                    .Rows(lngRow).Hidden = True
                End If
            End If
        End If
    Next
    .Protect Password:="MyPassword"
End With
End Sub

Sub UnHideRows()
ActiveSheet.Cells.EntireRow.Hidden = False
End Sub

Select allOpen in new window

Hi, That was brilliant! At least, it is as far as hiding the rows goes, but when I press Ctrl+Shift+U, I get a Run-time error message as follows:-

Run-time error '1004'
Unable to set the Hidden property of the Range column
END   DEBUG   HELP

when I press 'Debug' it shows me a yellow-highlighted part of your code - the line:-

ActiveSheet.Cells.EntireRow.Hidden = False

I can't then unhide anything, and that can't seem to be reversed.
Any clues? The hide bit is genius, though!!
If only I could learn VBA too! I expect i could, but it's finding the right book . . . .

But thanks for all this so far - magic!

That seems to have done it. Perfect. Thanks so much. Now, down to learning VBA!
Thanks again - your help has been much appreciated.
Best regards,
Richard

Very supportive and patient with my lack of VBA knowledge. Really was such a huge help. Has spurred me into starting to learn VBA. This is what is good about EE!

You're welcome and I'm glad I was able to help, and thank you for the kind words.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014