Optimization of Code

V

vandango05

Board Regular
Joined
Oct 6, 2008
Messages
110
Hi.

I've written some code which seems a bit unwieldy for my excel sheet to hide/unhide rows based on criteria. The sheet flashes for some time after each cell is written in. Can anyone give me any ideas how to optimize this?

Private Sub Worksheet_Calculate()


Dim MyResult As String
Sheet3.Unprotect


Application.EnableEvents = False

Rows("1:" & Sheet3.UsedRange.Rows.Count).EntireRow.Hidden = False

MyResult = Sheet3.Cells(7, 3).Value

Select Case MyResult



Case "XO", "OX"
Rows("32:37").EntireRow.Hidden = True
Rows("40").EntireRow.Hidden = True
Rows("42").EntireRow.Hidden = True
Rows("44").EntireRow.Hidden = True
Rows("48:53").EntireRow.Hidden = True
Rows("54:71").EntireRow.Hidden = True
Rows("77").EntireRow.Hidden = True
Rows("90:95").EntireRow.Hidden = True
Rows("134").EntireRow.Hidden = True
Rows("175").EntireRow.Hidden = True

Case "XX"
Rows("32:37").EntireRow.Hidden = True
Rows("40").EntireRow.Hidden = True
Rows("42").EntireRow.Hidden = True
Rows("44").EntireRow.Hidden = True
Rows("48:53").EntireRow.Hidden = True
Rows("54:71").EntireRow.Hidden = True
Rows("73").EntireRow.Hidden = True
Rows("77").EntireRow.Hidden = True
Rows("90:95").EntireRow.Hidden = True
Rows("112").EntireRow.Hidden = True
Rows("114:115").EntireRow.Hidden = True
Rows("126").EntireRow.Hidden = True
Rows("132").EntireRow.Hidden = True
Rows("133").EntireRow.Hidden = True
Rows("134").EntireRow.Hidden = True
Rows("173").EntireRow.Hidden = True
Rows("174").EntireRow.Hidden = True
Rows("175").EntireRow.Hidden = True
Rows("167").EntireRow.Hidden = True
Rows("155:156").EntireRow.Hidden = True

Case "XXP"
Rows("33:37").EntireRow.Hidden = True
Rows("42").EntireRow.Hidden = True
Rows("49:53").EntireRow.Hidden = True
Rows("55").EntireRow.Hidden = True
Rows("57:59").EntireRow.Hidden = True
Rows("61").EntireRow.Hidden = True
Rows("63:65").EntireRow.Hidden = True
Rows("67").EntireRow.Hidden = True
Rows("69:71").EntireRow.Hidden = True
Rows("73").EntireRow.Hidden = True
Rows("77").EntireRow.Hidden = True
Rows("91:95").EntireRow.Hidden = True
Rows("112").EntireRow.Hidden = True
Rows("114:115").EntireRow.Hidden = True
Rows("126").EntireRow.Hidden = True
Rows("132").EntireRow.Hidden = True
Rows("133").EntireRow.Hidden = True
Rows("173").EntireRow.Hidden = True
Rows("174").EntireRow.Hidden = True
Rows("167").EntireRow.Hidden = True
Rows("155:156").EntireRow.Hidden = True

Case "PXX"
Rows("33:37").EntireRow.Hidden = True
Rows("42").EntireRow.Hidden = True
Rows("49:53").EntireRow.Hidden = True
Rows("56:59").EntireRow.Hidden = True
Rows("62:65").EntireRow.Hidden = True
Rows("68:71").EntireRow.Hidden = True
Rows("73").EntireRow.Hidden = True
Rows("77").EntireRow.Hidden = True
Rows("91:95").EntireRow.Hidden = True
Rows("112").EntireRow.Hidden = True
Rows("114:115").EntireRow.Hidden = True
Rows("126").EntireRow.Hidden = True
Rows("132").EntireRow.Hidden = True
Rows("133").EntireRow.Hidden = True
Rows("173").EntireRow.Hidden = True
Rows("174").EntireRow.Hidden = True
Rows("167").EntireRow.Hidden = True
Rows("155:156").EntireRow.Hidden = True

Case "XXO", "OXX"
Rows("33:36").EntireRow.Hidden = True
Rows("40").EntireRow.Hidden = True
Rows("42").EntireRow.Hidden = True
Rows("44").EntireRow.Hidden = True
Rows("49:53").EntireRow.Hidden = True
Rows("54:71").EntireRow.Hidden = True
Rows("73").EntireRow.Hidden = True
Rows("77").EntireRow.Hidden = True
Rows("91:95").EntireRow.Hidden = True
Rows("134").EntireRow.Hidden = True
Rows("112").EntireRow.Hidden = True
Rows("126").EntireRow.Hidden = True
Rows("175").EntireRow.Hidden = True
Rows("167").EntireRow.Hidden = True

Case "XXM", "MXX"
Rows("33:36").EntireRow.Hidden = True
Rows("40").EntireRow.Hidden = True
Rows("42").EntireRow.Hidden = True
Rows("44").EntireRow.Hidden = True
Rows("49:53").EntireRow.Hidden = True
Rows("54:71").EntireRow.Hidden = True
Rows("73").EntireRow.Hidden = True
Rows("77").EntireRow.Hidden = True
Rows("91:95").EntireRow.Hidden = True
Rows("134").EntireRow.Hidden = True
Rows("112").EntireRow.Hidden = True
Rows("114:115").EntireRow.Hidden = True
Rows("126").EntireRow.Hidden = True
Rows("132").EntireRow.Hidden = True
Rows("133").EntireRow.Hidden = True
Rows("173").EntireRow.Hidden = True
Rows("174").EntireRow.Hidden = True
Rows("175").EntireRow.Hidden = True
Rows("167").EntireRow.Hidden = True
Rows("155:156").EntireRow.Hidden = True

Case "OLO", "ORO"
Rows("33:37").EntireRow.Hidden = True
Rows("40").EntireRow.Hidden = True
Rows("42").EntireRow.Hidden = True
Rows("44").EntireRow.Hidden = True
Rows("49:53").EntireRow.Hidden = True
Rows("54:71").EntireRow.Hidden = True
Rows("91:95").EntireRow.Hidden = True
Rows("134").EntireRow.Hidden = True
Rows("175").EntireRow.Hidden = True

Case "OLF", "FRO"
Rows("33:37").EntireRow.Hidden = True
Rows("40").EntireRow.Hidden = True
Rows("42").EntireRow.Hidden = True
Rows("44").EntireRow.Hidden = True
Rows("49:53").EntireRow.Hidden = True
Rows("54:71").EntireRow.Hidden = True
Rows("91:95").EntireRow.Hidden = True
Rows("134").EntireRow.Hidden = True
Rows("175").EntireRow.Hidden = True

Case "OXMO", "OMXO"
Rows("34:37").EntireRow.Hidden = True
Rows("40").EntireRow.Hidden = True
Rows("44").EntireRow.Hidden = True
Rows("50:70").EntireRow.Hidden = True
Rows("76").EntireRow.Hidden = True
Rows("92:95").EntireRow.Hidden = True
Rows("134").EntireRow.Hidden = True
Rows("175").EntireRow.Hidden = True

Case "OXMX", "XXMO", "OMXX", "XMXO", "MXXO"
Rows("34:37").EntireRow.Hidden = True
Rows("40").EntireRow.Hidden = True
Rows("44").EntireRow.Hidden = True
Rows("50:71").EntireRow.Hidden = True
Rows("73").EntireRow.Hidden = True
Rows("92:95").EntireRow.Hidden = True
Rows("112").EntireRow.Hidden = True
Rows("126").EntireRow.Hidden = True
Rows("134").EntireRow.Hidden = True
Rows("175").EntireRow.Hidden = True
Rows("167").EntireRow.Hidden = True

Case "XXMX", "XMXX", "MXXX", "XXXM"
Rows("34:37").EntireRow.Hidden = True
Rows("40").EntireRow.Hidden = True
Rows("44").EntireRow.Hidden = True
Rows("50:71").EntireRow.Hidden = True
Rows("73").EntireRow.Hidden = True
Rows("92:95").EntireRow.Hidden = True
Rows("112").EntireRow.Hidden = True
Rows("114:115").EntireRow.Hidden = True
Rows("126").EntireRow.Hidden = True
Rows("132").EntireRow.Hidden = True
Rows("134").EntireRow.Hidden = True
Rows("133").EntireRow.Hidden = True
Rows("173").EntireRow.Hidden = True
Rows("174").EntireRow.Hidden = True
Rows("175").EntireRow.Hidden = True
Rows("167").EntireRow.Hidden = True
Rows("155:156").EntireRow.Hidden = True

Case "XXXP"
Rows("34:37").EntireRow.Hidden = True
Rows("42").EntireRow.Hidden = True
Rows("50:53").EntireRow.Hidden = True
Rows("55:56").EntireRow.Hidden = True
Rows("58:59").EntireRow.Hidden = True
Rows("61:62").EntireRow.Hidden = True
Rows("64:65").EntireRow.Hidden = True
Rows("67:68").EntireRow.Hidden = True
Rows("70:71").EntireRow.Hidden = True
Rows("73").EntireRow.Hidden = True
Rows("77").EntireRow.Hidden = True
Rows("92:95").EntireRow.Hidden = True
Rows("112").EntireRow.Hidden = True
Rows("114:115").EntireRow.Hidden = True
Rows("126").EntireRow.Hidden = True
Rows("132").EntireRow.Hidden = True
Rows("133").EntireRow.Hidden = True
Rows("173").EntireRow.Hidden = True
Rows("174").EntireRow.Hidden = True
Rows("167").EntireRow.Hidden = True
Rows("155:156").EntireRow.Hidden = True

Case "PXXX"
Rows("34:37").EntireRow.Hidden = True
Rows("42").EntireRow.Hidden = True
Rows("50:53").EntireRow.Hidden = True
Rows("56:59").EntireRow.Hidden = True
Rows("62:65").EntireRow.Hidden = True
Rows("68:71").EntireRow.Hidden = True
Rows("73").EntireRow.Hidden = True
Rows("77").EntireRow.Hidden = True
Rows("92:95").EntireRow.Hidden = True
Rows("112").EntireRow.Hidden = True
Rows("114:115").EntireRow.Hidden = True
Rows("126").EntireRow.Hidden = True
Rows("132").EntireRow.Hidden = True
Rows("133").EntireRow.Hidden = True
Rows("173").EntireRow.Hidden = True
Rows("174").EntireRow.Hidden = True
Rows("167").EntireRow.Hidden = True
Rows("155:156").EntireRow.Hidden = True

Case "PXXMXP", "PXMXXP"
Rows("36:37").EntireRow.Hidden = True
Rows("52:53").EntireRow.Hidden = True
Rows("56:57").EntireRow.Hidden = True
Rows("59").EntireRow.Hidden = True
Rows("62:63").EntireRow.Hidden = True
Rows("65").EntireRow.Hidden = True
Rows("68:69").EntireRow.Hidden = True
Rows("71").EntireRow.Hidden = True
Rows("73").EntireRow.Hidden = True
Rows("76").EntireRow.Hidden = True
Rows("94:95").EntireRow.Hidden = True
Rows("112").EntireRow.Hidden = True
Rows("114:115").EntireRow.Hidden = True
Rows("126").EntireRow.Hidden = True
Rows("132").EntireRow.Hidden = True
Rows("133").EntireRow.Hidden = True
Rows("173").EntireRow.Hidden = True
Rows("174").EntireRow.Hidden = True
Rows("167").EntireRow.Hidden = True
Rows("155:156").EntireRow.Hidden = True

Case "OXXMXO", "OXMXXO"
Rows("36:37").EntireRow.Hidden = True
Rows("40").EntireRow.Hidden = True
Rows("44").EntireRow.Hidden = True
Rows("52:71").EntireRow.Hidden = True
Rows("73").EntireRow.Hidden = True
Rows("75").EntireRow.Hidden = True
Rows("94:95").EntireRow.Hidden = True
Rows("134").EntireRow.Hidden = True
Rows("112").EntireRow.Hidden = True
Rows("126").EntireRow.Hidden = True
Rows("175").EntireRow.Hidden = True
Rows("167").EntireRow.Hidden = True

Case "XXXMXX", "XXMXXX"
Rows("36:37").EntireRow.Hidden = True
Rows("40").EntireRow.Hidden = True
Rows("44").EntireRow.Hidden = True
Rows("52:71").EntireRow.Hidden = True
Rows("73").EntireRow.Hidden = True
Rows("94:95").EntireRow.Hidden = True
Rows("112").EntireRow.Hidden = True
Rows("114:115").EntireRow.Hidden = True
Rows("134").EntireRow.Hidden = True
Rows("126").EntireRow.Hidden = True
Rows("132").EntireRow.Hidden = True
Rows("133").EntireRow.Hidden = True
Rows("173").EntireRow.Hidden = True
Rows("174").EntireRow.Hidden = True
Rows("175").EntireRow.Hidden = True
Rows("167").EntireRow.Hidden = True
Rows("155:156").EntireRow.Hidden = True

Case "PXXXMXXP", "PXXMXXXP"
Rows("56:58").EntireRow.Hidden = True
Rows("62:64").EntireRow.Hidden = True
Rows("68:70").EntireRow.Hidden = True
Rows("73").EntireRow.Hidden = True
Rows("76").EntireRow.Hidden = True
Rows("112").EntireRow.Hidden = True
Rows("114:115").EntireRow.Hidden = True
Rows("126").EntireRow.Hidden = True
Rows("132").EntireRow.Hidden = True
Rows("133").EntireRow.Hidden = True
Rows("173").EntireRow.Hidden = True
Rows("174").EntireRow.Hidden = True
Rows("167").EntireRow.Hidden = True
Rows("155:156").EntireRow.Hidden = True

End Select

Dual = Sheet3.Cells(18, 3).Value

Select Case Dual

Case "Single Colour"
Rows("20").EntireRow.Hidden = True

End Select

GRP = Sheet3.Cells(14, 3).Value

Select Case GRP

Case "No Reinforcing Profile"
Rows("41").EntireRow.Hidden = True
Rows("120:121").EntireRow.Hidden = True
Rows("161:162").EntireRow.Hidden = True

End Select

Glasspanes = Sheet3.Cells(88, 2).Value

Select Case Glasspanes

Case "All Panels"
Rows("89:95").EntireRow.Hidden = True

End Select
Com119 = Sheet3.Cells(120, 3).Value

Select Case Com119

Case "0", ""
Rows("120").EntireRow.Hidden = True

End Select

Com120 = Sheet3.Cells(121, 3).Value

Select Case Com120

Case "0", ""
Rows("121").EntireRow.Hidden = True

End Select

Headaddon = Sheet3.Cells(15, 3).Value

Select Case Headaddon

Case "0"
Rows("78").EntireRow.Hidden = True
Rows("129").EntireRow.Hidden = True
Rows("170").EntireRow.Hidden = True

End Select

Flushdetail = Sheet3.Cells(13, 3).Value

Select Case Flushdetail

Case "N"
Rows("79:80").EntireRow.Hidden = True
Rows("128").EntireRow.Hidden = True
Rows("169").EntireRow.Hidden = True

End Select

Sill = Sheet3.Cells(12, 3).Value

Select Case Sill

Case "No Sill"
Rows("81:82").EntireRow.Hidden = True
Rows("130:131").EntireRow.Hidden = True
Rows("171:172").EntireRow.Hidden = True
Case "155x30"
Rows("82").EntireRow.Hidden = True
Rows("130").EntireRow.Hidden = True
Rows("171").EntireRow.Hidden = True
Case "155x18"
Rows("82").EntireRow.Hidden = True
Rows("130").EntireRow.Hidden = True
Rows("171").EntireRow.Hidden = True
Case "190x18"
Rows("82").EntireRow.Hidden = True
Rows("130").EntireRow.Hidden = True
Rows("171").EntireRow.Hidden = True
End Select


Addonl = Sheet3.Cells(16, 3).Value

Select Case Addonl

Case "0"
Rows("84").EntireRow.Hidden = True

End Select

Addonr = Sheet3.Cells(17, 3).Value

Select Case Addonr

Case "0"
Rows("85").EntireRow.Hidden = True

End Select

Trickle = Sheet3.Cells(11, 3).Value

Select Case Trickle

Case "No Trickle Vent"
Rows("135").EntireRow.Hidden = True
Rows("176").EntireRow.Hidden = True

End Select

Comp101 = Sheet3.Cells(102, 8).Value
Select Case Comp101
Case "N"
Rows("143").EntireRow.Hidden = True
End Select

Comp102 = Sheet3.Cells(103, 8).Value
Select Case Comp102
Case "N"
Rows("144").EntireRow.Hidden = True
End Select

Comp103 = Sheet3.Cells(104, 8).Value
Select Case Comp103
Case "N"
Rows("145").EntireRow.Hidden = True
End Select

Comp104 = Sheet3.Cells(105, 8).Value
Select Case Comp104
Case "N"
Rows("146").EntireRow.Hidden = True
End Select

Comp105 = Sheet3.Cells(106, 8).Value
Select Case Comp105
Case "N"
Rows("147").EntireRow.Hidden = True
End Select

Comp106 = Sheet3.Cells(107, 8).Value
Select Case Comp106
Case "N"
Rows("148").EntireRow.Hidden = True
End Select

Comp107 = Sheet3.Cells(108, 8).Value
Select Case Comp107
Case "N"
Rows("149").EntireRow.Hidden = True
End Select

Comp108 = Sheet3.Cells(109, 8).Value
Select Case Comp108
Case "N"
Rows("150").EntireRow.Hidden = True
End Select

Comp109 = Sheet3.Cells(110, 8).Value
Select Case Comp109
Case "N"
Rows("151").EntireRow.Hidden = True
End Select

Comp110 = Sheet3.Cells(111, 8).Value
Select Case Comp110
Case "N"
Rows("152").EntireRow.Hidden = True
End Select

Comp111 = Sheet3.Cells(112, 8).Value
Select Case Comp111
Case "N"
Rows("153").EntireRow.Hidden = True
End Select

Comp112 = Sheet3.Cells(113, 8).Value
Select Case Comp112
Case "N"
Rows("154").EntireRow.Hidden = True
End Select

Comp113 = Sheet3.Cells(114, 8).Value
Select Case Comp113
Case "N"
Rows("155").EntireRow.Hidden = True
End Select

Comp114 = Sheet3.Cells(115, 8).Value
Select Case Comp114
Case "N"
Rows("156").EntireRow.Hidden = True
End Select

Comp115 = Sheet3.Cells(116, 8).Value
Select Case Comp115
Case "N"
Rows("157").EntireRow.Hidden = True
End Select

Comp116 = Sheet3.Cells(117, 8).Value
Select Case Comp116
Case "N"
Rows("158").EntireRow.Hidden = True
End Select

Comp117 = Sheet3.Cells(118, 8).Value
Select Case Comp117
Case "N"
Rows("159").EntireRow.Hidden = True
End Select

Comp118 = Sheet3.Cells(119, 8).Value
Select Case Comp118
Case "N"
Rows("160").EntireRow.Hidden = True
End Select

Comp119 = Sheet3.Cells(120, 8).Value
Select Case Comp119
Case "N"
Rows("161").EntireRow.Hidden = True
End Select

Comp120 = Sheet3.Cells(121, 8).Value
Select Case Comp120
Case "N"
Rows("162").EntireRow.Hidden = True
End Select

Comp121 = Sheet3.Cells(122, 8).Value
Select Case Comp121
Case "N"
Rows("163").EntireRow.Hidden = True
End Select

Comp122 = Sheet3.Cells(123, 8).Value
Select Case Comp122
Case "N"
Rows("164").EntireRow.Hidden = True
End Select

Comp123 = Sheet3.Cells(124, 8).Value
Select Case Comp123
Case "N"
Rows("165").EntireRow.Hidden = True
End Select

Comp124 = Sheet3.Cells(125, 8).Value
Select Case Comp124
Case "N"
Rows("166").EntireRow.Hidden = True
End Select

Comp125 = Sheet3.Cells(126, 8).Value
Select Case Comp125
Case "N"
Rows("167").EntireRow.Hidden = True
End Select

Comp126 = Sheet3.Cells(127, 8).Value
Select Case Comp126
Case "N"
Rows("168").EntireRow.Hidden = True
End Select

Comp127 = Sheet3.Cells(128, 8).Value
Select Case Comp127
Case "N"
Rows("169").EntireRow.Hidden = True
End Select

Comp128 = Sheet3.Cells(129, 8).Value
Select Case Comp128
Case "N"
Rows("170").EntireRow.Hidden = True
End Select

Comp129 = Sheet3.Cells(130, 8).Value
Select Case Comp129
Case "N"
Rows("171").EntireRow.Hidden = True
End Select

Comp130 = Sheet3.Cells(131, 8).Value
Select Case Comp130
Case "N"
Rows("172").EntireRow.Hidden = True
End Select

Comp131 = Sheet3.Cells(132, 8).Value
Select Case Comp131
Case "N"
Rows("173").EntireRow.Hidden = True
End Select

Comp132 = Sheet3.Cells(133, 8).Value
Select Case Comp132
Case "N"
Rows("174").EntireRow.Hidden = True
End Select

Comp133 = Sheet3.Cells(134, 8).Value
Select Case Comp133
Case "N"
Rows("175").EntireRow.Hidden = True
End Select

Comp134 = Sheet3.Cells(135, 8).Value
Select Case Comp134
Case "N"
Rows("176").EntireRow.Hidden = True
End Select

Comp135 = Sheet3.Cells(136, 8).Value
Select Case Comp134
Case "N"
Rows("177").EntireRow.Hidden = True
End Select

Handle1 = Sheet3.Cells(182, 3).Value
Select Case Handle1
Case "", "0"
Rows("182").EntireRow.Hidden = True
End Select

Handle2 = Sheet3.Cells(183, 3).Value
Select Case Handle2
Case "", "0"
Rows("183").EntireRow.Hidden = True
End Select

Handle3 = Sheet3.Cells(184, 3).Value
Select Case Handle3
Case "", "0"
Rows("184").EntireRow.Hidden = True
End Select

Handle4 = Sheet3.Cells(185, 3).Value
Select Case Handle4
Case "", "0"
Rows("185").EntireRow.Hidden = True
End Select

Handle6 = Sheet3.Cells(186, 3).Value
Select Case Handle6
Case "", "0"
Rows("186").EntireRow.Hidden = True
End Select

Code1 = Sheet3.Cells(189, 3).Value
Select Case Code1
Case "", "0"
Rows("189").EntireRow.Hidden = True
End Select

Code2 = Sheet3.Cells(190, 3).Value
Select Case Code2
Case "", "0"
Rows("190").EntireRow.Hidden = True
End Select

Code3 = Sheet3.Cells(191, 3).Value
Select Case Code3
Case "", "0"
Rows("191").EntireRow.Hidden = True
End Select

Code4 = Sheet3.Cells(192, 3).Value
Select Case Code4
Case "", "0"
Rows("192").EntireRow.Hidden = True
End Select

Code5 = Sheet3.Cells(193, 3).Value
Select Case Code5
Case "", "0"
Rows("193").EntireRow.Hidden = True
End Select

Code6 = Sheet3.Cells(194, 3).Value
Select Case Code6
Case "", "0"
Rows("194").EntireRow.Hidden = True
End Select

Code7 = Sheet3.Cells(195, 3).Value
Select Case Code7
Case "", "0"
Rows("195").EntireRow.Hidden = True
End Select

Code8 = Sheet3.Cells(196, 3).Value
Select Case Code8
Case "", "0"
Rows("196").EntireRow.Hidden = True
End Select

Code9 = Sheet3.Cells(197, 3).Value
Select Case Code9
Case "", "0"
Rows("197").EntireRow.Hidden = True
End Select

Code10 = Sheet3.Cells(198, 3).Value
Select Case Code10
Case "", "0"
Rows("198").EntireRow.Hidden = True
End Select

Code11 = Sheet3.Cells(199, 3).Value
Select Case Code11
Case "", "0"
Rows("199").EntireRow.Hidden = True
End Select


Code12 = Sheet3.Cells(200, 3).Value
Select Case Code12
Case "", "0"
Rows("200").EntireRow.Hidden = True
End Select


Code13 = Sheet3.Cells(201, 3).Value
Select Case Code13
Case "", "0"
Rows("201").EntireRow.Hidden = True
End Select


Code14 = Sheet3.Cells(202, 3).Value
Select Case Code14
Case "", "0"
Rows("202").EntireRow.Hidden = True
End Select

Code15 = Sheet3.Cells(203, 3).Value
Select Case Code15
Case "", "0"
Rows("203").EntireRow.Hidden = True
End Select

Code16 = Sheet3.Cells(204, 3).Value
Select Case Code16
Case "", "0"
Rows("204").EntireRow.Hidden = True
End Select


Application.EnableEvents = True

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
I have no intention of streamlining your code, but you could insert

Application.ScreenUpdating = false

as one of the early lines, that would stop the screen flicker.
 
Upvote 0
Try wrapping the procedure with:

Application.screenupdating = false
'Code here
Application.screenupdating = True
 
Upvote 0
Thank you both for your reply, flicker has stopped my colleague who I'm making this for will just have to stare at the egg timer or upgrade his computer :)
 
Upvote 0
vandango05,

Please spend time at loops, for instance looping through a range of cells, and taking appropriate measures if certain conditions (IF formulas) are met or not. It will get rid of many many lines of code.

Also, statements like .EntireRow in
Code: 
Rows("32:37").EntireRow.Hidden = True
escape me totally. You use a full row, to which you apply again the EntireRow.

Rather than telling users to wait, you could improve the code and your own knowledge.
 
Last edited:
Upvote 0
Thanks Wigi, I've never learnt any VBA just picked up bits from very helpful forums, like this one, to try and compile a worksheet for a colleague. I'm afraid I don't understand loops etc. I'll do some more reading and try to learn.
 
Upvote 0
Also, code like this:

Code: 
Sill = Sheet3.Cells(12, 3).Value

Select Case Sill

Case "No Sill"
Rows("81:82").EntireRow.Hidden = True
Rows("130:131").EntireRow.Hidden = True
Rows("171:172").EntireRow.Hidden = True
Case "155x30"
Rows("82").EntireRow.Hidden = True
Rows("130").EntireRow.Hidden = True
Rows("171").EntireRow.Hidden = True
Case "155x18"
Rows("82").EntireRow.Hidden = True
Rows("130").EntireRow.Hidden = True
Rows("171").EntireRow.Hidden = True
Case "190x18"
Rows("82").EntireRow.Hidden = True
Rows("130").EntireRow.Hidden = True
Rows("171").EntireRow.Hidden = True
End Select

Why not:

Code: 
Select Case Sheet3.Cells(12, 3).Value
Case "No Sill": Range("81:82,130:131,171:172").EntireRow.Hidden = True
Case "155x30", "155x18", "190x18": Range("81:81,130:130,171:171").EntireRow.Hidden = True
End Select
 
Upvote 0
Thanks again, I did try something similar but couldn't get it to work. I now realise I was writing:

Case "155x30", "155x18", "190x18": Range("81:81,130,171").EntireRow

Didn't now I had to put 130:130 etc.

Thank you you've been very helpful. I'll make these changes which should speed things up.
 
Upvote 0
Or what about this code:

Code: 
Sub testtt()

    Code1 = Sheet3.Cells(189, 3).Value
    Select Case Code1
    Case "", "0"
        Rows("189").EntireRow.Hidden = True
    End Select

    Code2 = Sheet3.Cells(190, 3).Value
    Select Case Code2
    Case "", "0"
        Rows("190").EntireRow.Hidden = True
    End Select

    Code3 = Sheet3.Cells(191, 3).Value
    Select Case Code3
    Case "", "0"
        Rows("191").EntireRow.Hidden = True
    End Select

    Code4 = Sheet3.Cells(192, 3).Value
    Select Case Code4
    Case "", "0"
        Rows("192").EntireRow.Hidden = True
    End Select

    Code5 = Sheet3.Cells(193, 3).Value
    Select Case Code5
    Case "", "0"
        Rows("193").EntireRow.Hidden = True
    End Select

    Code6 = Sheet3.Cells(194, 3).Value
    Select Case Code6
    Case "", "0"
        Rows("194").EntireRow.Hidden = True
    End Select

    Code7 = Sheet3.Cells(195, 3).Value
    Select Case Code7
    Case "", "0"
        Rows("195").EntireRow.Hidden = True
    End Select

    Code8 = Sheet3.Cells(196, 3).Value
    Select Case Code8
    Case "", "0"
        Rows("196").EntireRow.Hidden = True
    End Select

    Code9 = Sheet3.Cells(197, 3).Value
    Select Case Code9
    Case "", "0"
        Rows("197").EntireRow.Hidden = True
    End Select

    Code10 = Sheet3.Cells(198, 3).Value
    Select Case Code10
    Case "", "0"
        Rows("198").EntireRow.Hidden = True
    End Select

    Code11 = Sheet3.Cells(199, 3).Value
    Select Case Code11
    Case "", "0"
        Rows("199").EntireRow.Hidden = True
    End Select

    Code12 = Sheet3.Cells(200, 3).Value
    Select Case Code12
    Case "", "0"
        Rows("200").EntireRow.Hidden = True
    End Select

    Code13 = Sheet3.Cells(201, 3).Value
    Select Case Code13
    Case "", "0"
        Rows("201").EntireRow.Hidden = True
    End Select

    Code14 = Sheet3.Cells(202, 3).Value
    Select Case Code14
    Case "", "0"
        Rows("202").EntireRow.Hidden = True
    End Select

    Code15 = Sheet3.Cells(203, 3).Value
    Select Case Code15
    Case "", "0"
        Rows("203").EntireRow.Hidden = True
    End Select

    Code16 = Sheet3.Cells(204, 3).Value
    Select Case Code16
    Case "", "0"
        Rows("204").EntireRow.Hidden = True
    End Select

End Sub

turned into:

Code: 
Sub testtt()
    
    Dim rng As Range
    
    For Each rng In Sheet3.Cells(189, 3).Resize(16).Value
    
        If rng.Value = 0 Or rng.Value = "" Then rng.EntireRow.Hidden = True
    
    Next
    
End Sub

Good luck with your study.

Wigi
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Creating a 'Reset' button for a text form, plus additional buttons
Replies
2
Views
291
WEB78
W
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
1K
kevin9999
K
M
  • Question Question
Hide Rows based on cell with drop down values
Replies
1
Views
1K
kevin9999
K
A
  • Question Question
Using a cell on another worksheet to run my code?
Replies
1
Views
399
rlv01
R
A
  • Solved
how to protect sheets vba code
2
Replies
19
Views
1K
Scuba94
S

Forum statistics

Threads
1,224,585
Messages
6,179,704
Members
452,938
Latest member
babeneker

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top