VBA Excel Code to Collapse Rows Based on Column Value

Question

What VBA code should I use in Excel to produce the following algorithm?

1. Test column A for any rows having value = 0.
2. If a row of column A has value = 0, hide one row above to one row below the tested row.

I'm already familiar with collapsing rows using VBA, but I'm unsure how to test a column and hide rows that aren't strictly specified.

Thanks,

Robert

Answer 1

anonymous userMillot-6866

Try the following code

Sub HideRowsUboveAndBelowZero()
    Dim rngToProcess As Range
    Dim rng As Range
    Dim rngRowsToHide As Range

    With Worksheets("Sheet1")       'Edit "Sheet1" to your sheet name
        'Following line assumes column headers in row 1 so commences row 2
        Set rngToProcess = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))

        For Each rng In rngToProcess
            If rng.Value = 0 Then
                If rngRowsToHide Is Nothing Then
                    If rng.Row < 3 Then 'Less than row 3 do not hide column header
                        Set rngRowsToHide = .Range(.Cells(rng.Row, rng.Column), .Cells(rng.Row + 1, rng.Column))
                    Else
                        Set rngRowsToHide = .Range(.Cells(rng.Row - 1, rng.Column), .Cells(rng.Row + 1, rng.Column))
                    End If
                Else
                        Set rngRowsToHide = Union(rngRowsToHide, .Range(.Cells(rng.Row - 1, rng.Column), .Cells(rng.Row + 1, rng.Column)))
                End If
            End If
        Next rng
    End With

    rngRowsToHide.Rows.EntireRow.Hidden = True
End Sub

Answer 2

OK. I will modify to test if not blank but equals zero.

Your other condition "I want the code to be in effect at all times, not just when I run it." I am assuming that you mean if a user enters a zero so please confirm if my assumption is correct and if not, how does it become zero. I can create event code to run when the user enters a zero in column A.

Answer 3

OK. I will modify to test if not blank but equals zero.

Your other condition "I want the code to be in effect at all times, not just when I run it." I am assuming that you mean if a user enters a zero so please confirm if my assumption is correct and if not, how does it become zero. I can create event code to run when the user enters a zero in column A.

Answer 4

OK so I am assuming that there is a formula in the cells in column A. If my assumption is not correct then please advise.

I believe that I can provide code that will run when the value changes but performance might be affected if the workbook is very large and a lot of calculations taking place so please advise on that.

What other ranges does the user change to get the formula in column A to change. It might be better to work on those changes and then test the result in column A.

Answer 5

It is now appearing to be a much more complex problem and it will be best if you can you upload an example workbook to OneDrive and then I can determine the best way to attack the problem. If your workbook contains sensitive data then make a copy and replace the sensitive data with dummy data.

In case it is required, the following are guidelines to upload a workbook to OneDrive. Note that sharing a link to a specific file on OneDrive does not give me access to any other files on your OneDrive and in fact I cannot even see the file list. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

Sharing links to business OneDrive often does not work because the business has applied security measures that prevent this. Some people upload from their own private OneDrive.

1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
3. Do not use 3rd party compression applications because I cannot unzip them.
4. Go to this link. https://onedrive.live.com
5. Use the same login Id and Password that you use for this forum.
6. Select Upload under the blue bar across the top and browse to the zipped folder to be uploaded.
7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
8. Right click the file name in OneDrive.
9. Select Share.
10. Click the link icon (Looks like chain links) at the bottom left of the dialog (Just above "Copy link").
11. Click Copy button.
12. Change back to this forum and paste the link.