Worksheet on change code a bit slow

[slang]

I have a sheet that rows hide based on values entered into specific cells.
The problem is that there are 30 values pasted into this sheet from a database when the sheet is activated which makes the code run for each cell pasted.
Here is the on change code

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="donttouch"
rows("5").EntireRow.Hidden = Range("e4").Value < 1
rows("8").EntireRow.Hidden = Range("e7").Value < 1
rows("11:12").EntireRow.Hidden = Range("e10").Value < 1
rows("15:17").EntireRow.Hidden = Range("e14").Value < 1
rows("20:22").EntireRow.Hidden = Range("e19").Value < 1
rows("25").EntireRow.Hidden = Range("e24").Value <> "y"
rows("32").EntireRow.Hidden = Range("e31").Value <> "y"
rows("35:36").EntireRow.Hidden = Range("e34").Value <> "y"
ActiveSheet.Protect Password:="donttouch", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

Here is the sheet activate code
I know, long hand at the best:blush

Private Sub Worksheet_Activate()
ActiveSheet.Unprotect Password:="donttouch"
Range("E4").Select
Range("e4").Value = Sheets("questions").Range("w192")
Range("e5").Value = Sheets("questions").Range("x192")
Range("e7").Value = Sheets("questions").Range("y192")
Range("e8").Value = Sheets("questions").Range("z192")
Range("e10").Value = Sheets("questions").Range("aa192")
Range("e11").Value = Sheets("questions").Range("ab192")
Range("e12").Value = Sheets("questions").Range("ac192")
Range("e14").Value = Sheets("questions").Range("ad192")
Range("e15").Value = Sheets("questions").Range("ae192")
Range("e16").Value = Sheets("questions").Range("af192")
Range("e17").Value = Sheets("questions").Range("ag192")
Range("e19").Value = Sheets("questions").Range("ah192")
Range("e20").Value = Sheets("questions").Range("ai192")
Range("e21").Value = Sheets("questions").Range("aj192")
Range("e22").Value = Sheets("questions").Range("ak192")
Range("e24").Value = Sheets("questions").Range("al192")
Range("e25").Value = Sheets("questions").Range("am192")
Range("e26").Value = Sheets("questions").Range("an192")
Range("e28").Value = Sheets("questions").Range("ao192")
Range("e29").Value = Sheets("questions").Range("ap192")
Range("e31").Value = Sheets("questions").Range("aq192")
Range("c33").Value = Sheets("questions").Range("ar192")
Range("e38").Value = Sheets("questions").Range("as192")
Range("c44").Value = Sheets("questions").Range("av192")

Range("E4").Select
ActiveSheet.Protect Password:="donttouch", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells


Is there a cleaner way to do this or stop the on change code until all the values are updated?:dunno
Its a monster..
Thanks oh gods of Excel.

[snb]

Private Sub Worksheet_Activate()
ActiveSheet.Unprotect Password:="donttouch"

sn=Sheets("questions").Range("w192:AV192")
sp=range("E4:E38")

for j=1 to ubound(sp)
sp(j +iif(j>2,1,0)+iif(j>4,1,0)+iif(j>7,1,0)+ iif(j>11,1,0)+ iif(j>15,1,0)+ iif(j>18,1,0)+iif(j>20,1,0)+iif(j>21,1,0)+iif(j>22,4,0)+iif(j>23,5,0),1-(iif(j=22 or j=24,2,0))=sn(1,j)
next
range("E4:E38")=sn

ActiveSheet.Protect Password:="donttouch", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
end sub

[slang]

:bug:
OK, the rudimentry code I have posted should give you an idea of my coding level so please help me get my head around your code as there are a few things in there I have never seen before.:ack:
Edit, OK your using an array here which is a first for me but one must learn the hard ones sometime. Also there seems to be something missing in this line as it is a compile error.
sp(j +iif(j>2,1,0)+iif(j>4,1,0)+iif(j>7,1,0)+ iif(j>11,1,0)+ iif(j>15,1,0)+ iif(j>18,1,0)+iif(j>20,1,0)+iif(j>21,1,0)+iif(j>22,4,0)+iif(j>23,5,0),1-(iif(j=22 Or j=24,2,0))=sn(1,j)

I am assuming the code in the middle replaces to values in the target sheet with the values from the source sheet all in one action so the worksheet_Change code only fires once?
As for the code itself:think: , Can you give me a little explaination of how it functions? Dont need a manual just high level so I can understand it a bit better as I have several sheets that I would need to adopt this to.

Hate to say it but this is the first time I have seen an iif statement. I may need a personal floatational device for this one.
:Thinkingo

Thanks for the help.

[snb]

I am assuming the code in the middle replaces to values in the target sheet with the values from the source sheet all in one action so the worksheet_Change code only fires once?


That's the essence.
The fewer writing operations, the faster the code.

In your case it would be nice to restructure the sheet so you won't need this unnecessary complicated code. Structuring precedes coding: the more efficient the sheet's structure, the less code you need to manipulate the data in it.
The simplest way would have been:

activesheet.range("E4:E28")=application.transpose(sheets("questions").range("W192:AV192").value)