Any Better Ideas for Hiding Rows

[vandango05]

Hi

I've used the forum to create some code that hides particular rows based on a validation list in cell C6 here is the code:


Private Sub Worksheet_Calculate()
Dim MyResult As String

Application.EnableEvents = False

Rows("1:" & Worksheets("Cutting Sheet for Std Systems").UsedRange.Rows.Count).EntireRow.Hidden = False

MyResult = Worksheets("Cutting Sheet for Std Systems").Cells(6, 3).Value

Select Case MyResult


Case "XO", "XX", "OX"
Rows("28:33").EntireRow.Hidden = True
Rows("36").EntireRow.Hidden = True
Rows("38").EntireRow.Hidden = True
Rows("44:49").EntireRow.Hidden = True
Rows("50:67").EntireRow.Hidden = True
Rows("72").EntireRow.Hidden = True
Rows("86:91").EntireRow.Hidden = True

Case "XXP"
Rows("29:33").EntireRow.Hidden = True
Rows("38").EntireRow.Hidden = True
Rows("45:49").EntireRow.Hidden = True
Rows("51").EntireRow.Hidden = True
Rows("53:55").EntireRow.Hidden = True
Rows("57").EntireRow.Hidden = True
Rows("59:61").EntireRow.Hidden = True
Rows("63").EntireRow.Hidden = True
Rows("65:67").EntireRow.Hidden = True
Rows("72").EntireRow.Hidden = True
Rows("87:91").EntireRow.Hidden = True




' (repeated for further cases or configurations, but deleted to shrink post)

End Select
Application.EnableEvents = True



End Sub

I have a couple of problems with it. The code works fine but I also need to add in to hide other rows based on values in C10, C11, C12 and some others. I have tried amending the code within a new event but it stops the first part of code working.

A second problem is that the undo function is no longer available when the code is inserted in the worksheet.

Finally the third problem is that when this is used my colleague who i'm making it for will probably rename the worksheet (despite me saying that he can't!)

Any ideas and help would be greatly appreciated.

 

[Weaver]

1. If the new operations have to be executed as part of the worksheet_calculate event, you need to put it all in the one subroutine

2. Not much you can do about this, although I've heard there are some scripts out there for restoring status pre macro, I've never used one. You could always save the sheet first, but that's going to slow things way down
3. If it's the active sheet (and I think it must be) you don't need the sheet name

MyResult = activesheet.Cells(6, 3).Value

Ought to do the job.

HTH

 

[General Ledger]

VD5,

Here is one way I hide rows of a spreadsheet based on any criteria.

I start with my spreadsheet laid out as needed. I add a column either on the far left or far right. I label it "SHOW". In that column on each row of details, I create a formula that results in one of two answers: SHOW or HIDE. An example might be, =If (Z5=O,"HIDE","SHOW")

I select the entire range of the SHOW/HIDE formulas plus the column label of SHOW. I turn on AutoFilter. Now I can use the AutoFilter drop down list to show rows with SHOW or HIDE or all rows.

Because the SHOW column is not inside my columns of data, I can exclude it from my print range.

It is not too fancy. You have to manually select the filter after the data changes to be sure you are displaying all needed rows. At least it is relatively simple and is guaranteed to work in any version of Excel.

Best of luck,

GL

 

[mikerickson]

...Finally the third problem is that when this is used my colleague who i'm making it for will probably rename the worksheet (despite me saying that he can't!)

Any ideas and help would be greatly appreciated.

Problem 3): Create a one cell named range mySpecialCell (hide the Name) and replace
Worksheets("Cutting Sheet for Std Systems")
with
Names("mySpecialCell").RefersToRange.Parent

The user can re-name and move sheets to their hearts delight.

 

[vandango05]

Thank you for the quick replies, might have to use your show/hide method if I can't get the code to work for problem 1.

Where would I start the next execution within my code is it before or after the:

End Select
Application.EnableEvents = True

and would I start it like this again:

MyResult = Worksheets("Cutting Sheet for Std Systems").Cells(10, 3).Value

Select Case MyResult

Case "Hide"
Rows("98:105").EntireRow.Hidden = True

etc .. ..

Thanks again.

 

[vandango05]

Ok, i've sussed where to continue the code although now when I change a value the sheet flashes very quickly for 30 seconds to a minute whilst it hides/shows appropriate rows. I guess It will only get worse with the more executions I add?

 

[jasonb75]

Ok, i've sussed where to continue the code although now when I change a value the sheet flashes very quickly for 30 seconds to a minute whilst it hides/shows appropriate rows. I guess It will only get worse with the more executions I add?

Private Sub Worksheet_Calculate()
    Application.Screenupdating = False
 
 
' your code here
 
    Application.Screenupdating = True
End Sub

Finally the third problem is that when this is used my colleague who i'm making it for will probably rename the worksheet (despite me saying that he can't!)

Use the sheet codename instead of tab name, that can only break if your colleague deletes the sheet.

http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

 

[vandango05]

Thank you everyone for all your help, it's given me plenty to get on with. I'm sure i'll be back for more help at sometime in the future!