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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

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:

Please Login or Register  to view this content.

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.

rvasquez,

If

Please Login or Register  to view this content.

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

Thanks again.

Are the cell's being changed formulas?

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

Please Login or Register  to view this content.

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.

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):

Please Login or Register  to view this content.

...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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

This seems to work really great except this one liner:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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!