Speeding up Macro that Hides Rows when Range ("B178:B477") contains a zero value

Here is my code and I am also linking this to a thread that directly helped me

Private Sub Worksheet_Calculate()

Application.ScreenUpdating = False

Dim rngCell As Range

For Each rngCell In Range("B178:B188")
  rngCell.EntireRow.Hidden = rngCell.Value = 0
Next rngCell

Application.ScreenUpdating = True

End Sub

The main problem here is the fact that I believe this macro is calculating line by line regardless of whether or not there is an actual change in the cell range. Is there a way (and I am brand new to VBA so be gentle) to set up a condition that will search out the actually changed (affected) cell(s) instead of a wholesale assumption? Thanks in advance for any help my way.


p.s. I should note that I am posting this because my spreadsheet hangs then crashes at present.




http://www.excelforum.com/excel-prog...ls-g1-lg1.html

Change your code to look like the following:

Private Sub Worksheet_Change(ByVal Target As Range)  'on the worksheet change
Target.EntireRow.Hidden = Target.Value = 0  'if the cell changed is equal to 0 then hide the cell's row

End Sub

I initially tried this set up but the problem with it is that it requires a button to actuate the code instead of a real-time awareness that a cell value has been changed.

Private Sub Worksheet_Change(ByVal Target As Range)  'on the worksheet change

Target.EntireRow.Hidden = Target.Value = 0  'if the cell changed is equal to 0 then hide the cell's row

 Dim rngCell As Range

        For Each rngCell In Range("B178:B477")
  
            rngCell.EntireRow.Hidden = rngCell.Value = 0
    
        Next rngCell



End Sub

To reiterate, this set up does not work for me even though I appreciate the attempt. Still up for new thoughts ideas.


I should also note that the cell range I initially posted was my test range. In reality it is much larger as indicated in this post.

rvasquez,

If

Target.EntireRow.Hidden = Target.Value = 0  'if the cell changed is equal to 0 then hide the cell's row

Then what is the equivalent for simultaneously allowing the cell's row to be unhidden if a cells value is not zero?

Thanks again.

Let's try this, not sure it will speed anything up but it's worth a try!

Private Sub Worksheet_Change(ByVal Target As Range)  'on the worksheet change

Application.ScreenUpdating = False  'set screen updating to false
    
Dim rngCell As Range, rng As Range  'declare variables

For Each rngCell In Range("B178:B188")    'loop through cells in column B from row 178 to 188
    Select Case rngCell.Value   'if the current cell in the loop's value is...
        Case 0  'zero then...
            If rng Is Nothing Then  'if rng has not been defined as a range then...
                Set rng = rngCell   'set rng equal to the current cell in the loop
            Else: Set rng = Union(rng, rngCell) 'if it has been defined add the current cell in the loop to the rng range
            End If  'end if rng statement
    End Select  'end select case rngcell value statement
Next rngCell    'move to next cell in the loop

Cells.EntireRow.AutoFit 'unhide all cells
    rng.Select  'select the cells in the rng range
        Selection.EntireRow.Hidden = True   'hide the entire rows of ever cell in the rng range

Application.ScreenUpdating = True   'reestablish screen updating



End Sub

yes the cell's being changed are all formulas.

Let me look over this code you posted and see how it works. I really appreciate you helping me out.

Maybe this added information will help me with the code that I need to make this work. I realize this code allows me to hide rows but I simultaneously need it to reveal rows that it might have previously hidden due to changes in the worksheet. I don't know what that should look like syntactically but in my own language this is another way I would describe it:

Private Sub Worksheet_Change(ByVal Target As Range)  'on the worksheet change

Application.ScreenUpdating = False

Target.EntireRow.Hidden = Target.Value = 0  'if the cell changed is equal to 0 then hide the cell's row

 Dim rngCell As Range

        For Each rngCell In Range("B178:B477")
  
            rngCell.EntireRow.Hidden = rngCell.Value = 0 = "True"      
            rngCell.EntireRow.Hidden = rngCell.Value > 0 = "False"

        Next rngCell

Application.ScreenUpdating = True

End Sub

I am trying to figure this out on my own but I am incapable at the level of knowledge I currently possess with VBA. A book is on the way however!

Thanks in advance for any help . I am very grateful.

Are the cell's being changed formulas?

That does not seem to be working. the zero values are still on the sheet and I can't seem to invoke this code by making changes to a cell's value (i.e. from 11 to 0).

The original code you sent seems to work but only in one direction. Is there any way to augment that one (see below):

Private Sub Worksheet_Change(ByVal Target As Range)  'on the worksheet change

Target.EntireRow.Hidden = Target.Value = 0  'if the cell changed is equal to 0 then hide the cell's row

 Dim rngCell As Range

        For Each rngCell In Range("B178:B477")
  
            rngCell.EntireRow.Hidden = rngCell.Value = 0
    
        Next rngCell



End Sub

...see my post from 12:48 yesterday for details.

Either way whichever direction you feel more comfortable taking is fine with me.

Okay try the following:

Private Sub Worksheet_Change(ByVal Target As Range)  'on the worksheet change

Application.ScreenUpdating = False

Target.EntireRow.Hidden = Target.Value = 0  'if the cell changed is equal to 0 then hide the cell's row

    Dim rngCell As Range
        
        With Sheets("Sheet2")
            
            For Each rngCell In .Range("B178:B477")

                rngCell.EntireRow.Hidden = rngCell.Value = 0
            
            Next rngCell
        
        End With

Application.ScreenUpdating = True

End Sub

wow, thank you ....

however, the range does not seem defined because now the entire worksheet Column B is having its rows hidden when I am manually changing a value.

I still haven't tested to see if this provides the proper mechanism within the defined range (where I want this code to only affect).

I can't wait until I have the proficiency you have.

Can you please post an example of your workbook and what your desired results are? It's a little hard to guess where your formulas are in the worksheet and exactly how it functions.

Thanks!

exampleworkbook.xlsx

Here is an attachment. It is a mock setup on a very simplistic level that should provide a very basic conceptual idea of what I am doing

I see what your code is doing now. It is not going through all cells in column B but rather starts the process if my cursor is on any active cell in column B , and then sequentially works through the code. I still do not prefer that behavior but I wanted to clarify my previous post.

additionally, it does still seem to be missing the mechanism that will auto actuate the code.

*************************[duplicate entry]****************************

Sorry, but I'm still a little confused as you haven't specified much. Below is what I understand of you workbook. If it is incorrect please correct it and also I have some questions as I go so please answer those so I can provide you with a correct code.

1. You are inputting values into worksheet Sheet1

2. When a value is inputted on worksheet Sheet1 then ....

Question1: Is there a particular column or restriction to where the changes are being made? This is a need to know as it determines the code for the worksheet on change event.

Question 2: Are the values in sheet1 maunual input?

3. If a cell's value on sheet1 is changed then with worksheet sheet2
4. Loop through the cells from cell B178 to B477 and if the value is then hide the entire row

Is this correct?

Thanks!

Answers by number:

1. Correct
2.
Q1&2 - sheet1 is manual entry (for the most part) with no restrictions as to where the data is located on sheet1.
Some of the input (sheet1) is affecting column B in sheet2 starting with B178 and ending with B477, while some of the input is affecting all other rows and columns on sheet2.
3. I am not following the wording of this question. Please rephrase.
4. Is correct if you mean "...if the value is [zero] then hide the entire..."


Thank....you. Don't thank me.

Q1. The cells on Sheet1 that determine whether or not Sheet2 cells B178 to B477 are hidden, what are those cells?

Q2. Do you want the code to run when a value is changed on Sheet1 or Sheet2?

1. manual entry cells
2. code to run when a value is changed on sheet1

Q1. The cells on Sheet1 that determine whether or not Sheet2 cells B178 to B477 are hidden, what are those cells?

Sorry, I meant what cells are they? Range?

The range for these cells are A1:AZ150

that covers the workable sheet.

Okay let's try this one:

This should go behind worksheet Sheet1. You do this by right clicking on the worksheet sheet1's tab and select view code. If there is a Private Sub Worksheet_Change(ByVal Target As Range) event currently in there please overwrite it with the following code

Private Sub Worksheet_Change(ByVal Target As Range) 'on the worksheet sheet1 change

Dim c As Range  'declare variables

If Target.Column = 1 Then   'if the changed cell in is column A (aka 1) then
    
    With Sheets("Sheet2")   'with Sheet2
        
        For Each c In .Range("B178:B477").Cells 'loop through cell from row 178 to 477 in column B
            
            If IsError(c.Value) Then    'if the current cell in loop's value is error then ...
                
                c.EntireRow.Hidden = False  'don't hide the current cell in the loop's row
                        
            'else if the current cell in the loop's value is 0 and is not just an empty cell then...
            ElseIf c.Value = 0 And c.Value <> vbNullString Then
                
                c.EntireRow.Hidden = True   'hide the current cell in the loop's row
            
            Else    'any other situations that don't meet the above conditions
                
                c.EntireRow.Hidden = False  'don't hide the current cell in the loop's row
            
            End If
        
        Next c  'move to next cell in the loop
    
    End With

End If
    
End Sub

I will test this out tomorrow afternoon. That was quick!

This seems to work really great except this one liner:

If Target.Column = 1 Then   'if the changed cell in is column A (aka 1) then

I tried changing it myself but I can't figure out syntax.

The Target.Column should equal the workable range on the sheet which is:

The range for these cells are A1:AZ150

that covers the workable sheet.

Just update your code to look like the following

Private Sub Worksheet_Change(ByVal Target As Range) 'on the worksheet sheet1 change

Dim c As Range  'declare variables
    
    With Sheets("Sheet2")   'with Sheet2
        
        For Each c In .Range("B178:B477").Cells 'loop through cell from row 178 to 477 in column B
            
            If IsError(c.Value) Then    'if the current cell in loop's value is error then ...
                
                c.EntireRow.Hidden = False  'don't hide the current cell in the loop's row
                        
            'else if the current cell in the loop's value is 0 and is not just an empty cell then...
            ElseIf c.Value = 0 And c.Value <> vbNullString Then
                
                c.EntireRow.Hidden = True   'hide the current cell in the loop's row
            
            Else    'any other situations that don't meet the above conditions
                
                c.EntireRow.Hidden = False  'don't hide the current cell in the loop's row
            
            End If
        
        Next c  'move to next cell in the loop
    
    End With
    
End Sub

This seems to be working just great! Thank you for your time spent on this. I ended up creating a button for it, placing the code behind sheet2 and assigning macro to button. I only did that because I realized just how impractical it was for it do something after every cell entry. That is not your fault however but mine; you did exactly as we discussed. Thanks a million man!

No problem, don't forget to mark this thread solved and maybe give a little star tap if I helped.

Thanks!