0

I want to hide some of the Rows in a worksheet if the Cell in column A contains 0. This is calculated using a formula.

E.g. If A1 contains value "0", row#1 should get hidden.

I know I can write this code on Sheet's Activate Event but it is slowing down the file's performance.

Is there a good way to achieve the same without compromising file's performance?

Improve this question

1 Answer 1

Reset to default
2

you could always use filter to achieve this. I think that would be much more efficient.

Edit: To improve performance of your code, you could do the following:

  1. Turn off the Events
  2. Turn off the Calculations

Before running the loop to hide rows

With Excel.Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual .Cursor = xlWait End With

After the loop is over

With Excel.Application .ScreenUpdating = True .EnableEvents = True .Calculation = xlCalculationAutomatic .Cursor = xlDefault End With

Hope this helps. Also explicitly using variables could give a boost to performance and Calculation. Although explicitly declaring your variables should help too.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Yes that is but I don't want the user to see that the data is filtered and neither want to allow them to change the data filter. Is it possible using code?
Not an answer - should have been posted as a comment
@brettdj: I beg to differ. The underlying question was "Is there a good way to achieve the same without compromising file's performance?". And using "filter" is one way to do the same. Please let me know in detail why you disagree.
In Detail? Beacuse a one line use Filter isn't an answer.
@Konstant: Thanks. I didn't make the calculation manual. Will try that as well. I'm sure that will speed it up!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.