Macro hides the wrong rows and is too slow

Here is my current macro:

Please Login or Register  to view this content.

First of all, when I click on the "Quote" tab there is some delay which I believe is due to the macro. Is there anything I can do to speed this up?

Secondly, I do not understand the above macro and how it works though I have spent countless hours trying to find something in this and other forums. I'm not sure if the macro hides or unhides rows. The user needs to be able to insert or delete rows ABOVE the hidden (or unhidden) rows without changing the range of hidden rows.

On the "Calculation" sheet, Rows 38-66, the user may select options by entering a quantity in Row A.

Attachment 313847

Then when the user clicks on the "Quote" sheet only the rows with the options that the user selected are visible.

Attachment 313852

In an Excel spreadsheet I would just use the dollar sign ($) to indicate absolute reference and be done with it but I don't understand how "B & i" refers to a specific range of rows. Is "B" the column and "i" the row? Also, I would like the "Available Options" row (on Quote sheet) to be visible only if the user has selected at least one option on the "Calculation" sheet.


Thanks for your help in helping me understand this.
Marvin

I think we need to go back to basics and for you to upload the workbook and explain your process. i.e. how you use the sheet and what you expect to happen when various actions take place. Manually add some calculated results, explain where they are and what rules have been applied in deriving the results.

Re your absolute Q.
B & i doesn't refer to a range of cells, only the single cell Bi where i is an integer starting at 45 and incrementing by one each time through the loop until the macro finds a blank cell below B45.

You probably don't want an absolute address in the looping section of code, but in general the syntax would be "$B$" & i

Hi,

Not quite sure I'm understanding this yet but here goes.
Are you saying that the process is to mark various rows in the Calculation sheet with positive quantities, and then have those rows, and only those rows appear on the Quote sheet?

If so then the simplest way is to just use a Data Filter in a macro. There would be no hidden rows on the Quote sheet, just the rows that have been identified on the Calculation sheet.

Are the rows 1:42 on the Quote sheet and 1:36 on the Calculation sheet fixed and never changing?

Hi Marvelous

Will your Options on Sheet Calculation ALWAYS start on Row 38?

Will your Options on Sheet Quote ALWAYS start on Row 45?

Will there ALWAYS be 29 Options on each Sheet?

Hi Marvelous

Place this Code in Calculation Sheet Code Module

Please Login or Register  to view this content.

Hi Marvelous

That error message would indicate you've two Macro Procedures with the same Name. Since the File you provided had no Code in it I can't troubleshoot without seeing your File...and...Column B does not come into play with the File you provided...so you have me a bit confused.

Hi Marvelous

I have no clue what the "Old" Code is doing...this Compiles and colors some Cells...try it, let me know. Replace BOTH Worksheet Change Event Codes with this.

Please Login or Register  to view this content.