Suppressing a row in which a zero appears

I have a worksheet within a large spreadsheet that produces a large range of data in rows. One of the columns in this worksheet may produce a zero (in a specific, unvarying, column). When a zero appears in that column (let us say Column P) I want every row that contains this zero in column P, to be suppressed - to disappear, leaving no blank row.
Thus, although there may be 300 rows, it could be that on occasions I will end up with just 20 rows, because 270 of the rows have a zero at Column P. I need to say that not all the rows are identical - some of the rows have cells that have been merged to make headings, etc - so some of the rows don't have a Column P.
Hope that all makes sense.
I do not really want to put the spreadsheet on this site, for commercial reasons, but if need be I could write a little representative sample of what I'm talking about: but I think I've made it reasonably clear what I'm trying to achieve.
Thanks.
BlosMusicAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KimputerCommented:
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.
0
BlosMusicAuthor Commented:
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
0
KimputerCommented:
What should happen with these "empty" or "header" rows? Also hide (hope so, that's the easiest!)
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

BlosMusicAuthor Commented:
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!
0
Martin LissOlder than dirtCommented:
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?
0
BlosMusicAuthor Commented:
The total rows, yes - plus the headings, e.g. MEMBRANE ACCESSORIES, Doors, Window, and so on.
0
Martin LissOlder than dirtCommented:
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

Open in new window

0
Martin LissOlder than dirtCommented:
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

Open in new window

0
KimputerCommented:
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.
0
BlosMusicAuthor Commented:
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.
0
KimputerCommented:
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.
0
BlosMusicAuthor Commented:
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.
0
BlosMusicAuthor Commented:
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
0
KimputerCommented:
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)
0
Martin LissOlder than dirtCommented:
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

Open in new window

0
BlosMusicAuthor Commented:
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!
0
Martin LissOlder than dirtCommented:
Change the UnHideRows sub to the following, again substituting your password.

Sub UnHideRows()
With ActiveSheet
    .Unprotect Password:="MyPassword"
    .Cells.EntireRow.Hidden = False
    .Protect Password:="MyPassword"
End With
End Sub

Open in new window


The HideRows sub would be improved with the addition of lines 5 and 22.
Sub HideRows()
Dim lngLastRow As Long
Dim lngRow As Long

Application.ScreenUpdating = False

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

Application.ScreenUpdating = True
End Sub

Open in new window

As for learning VBA, there is this EE tutorial. It's the first of three parts and while it is written for Access, I'm sure 99% of it is good for Excel too. There are also a lot of VBA tutorials on the web.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BlosMusicAuthor Commented:
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
0
BlosMusicAuthor Commented:
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!
0
Martin LissOlder than dirtCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.