Only Part of Code Running

[vandango05]

I've written some code for a project and only part of it seems to run, here is the code:

Private Sub Worksheet_Calculate()


Dim MyResult As String

Application.ScreenUpdating = False
Application.EnableEvents = False

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


D1 = Sheet11.Cells(11, 2).Value
Select Case D1
Case "0", ""
Rows("24:32").EntireRow.Hidden = True
Case "2"
Rows("27:32").EntireRow.Hidden = True
Case "3"
Rows("28:32").EntireRow.Hidden = True
Case "4"
Rows("29:32").EntireRow.Hidden = True
Case "6"
Rows("31:32").EntireRow.Hidden = True
End Select

D2 = Sheet11.Cells(11, 3).Value
Select Case D2
Case "0", ""
Rows("33:41").EntireRow.Hidden = True
Case "2"
Rows("36:41").EntireRow.Hidden = True
Case "3"
Rows("37:41").EntireRow.Hidden = True
Case "4"
Rows("38:41").EntireRow.Hidden = True
Case "6"
Rows("40:41").EntireRow.Hidden = True
End Select

D3 = Sheet11.Cells(11, 4).Value
Select Case D3
Case "0", ""
Rows("42:50").EntireRow.Hidden = True
Case "2"
Rows("45:50").EntireRow.Hidden = True
Case "3"
Rows("46:50").EntireRow.Hidden = True
Case "4"
Rows("47:50").EntireRow.Hidden = True
Case "6"
Rows("49:50").EntireRow.Hidden = True
End Select

D4 = Sheet11.Cells(11, 5).Value
Select Case D4
Case "0", ""
Rows("51:59").EntireRow.Hidden = True
Case "2"
Rows("54:59").EntireRow.Hidden = True
Case "3"
Rows("55:59").EntireRow.Hidden = True
Case "4"
Rows("56:59").EntireRow.Hidden = True
Case "6"
Rows("58:59").EntireRow.Hidden = True
End Select

D5 = Sheet11.Cells(11, 6).Value
Select Case D5
Case "0", ""
Rows("60:68").EntireRow.Hidden = True
Case "2"
Rows("63:68").EntireRow.Hidden = True
Case "3"
Rows("64:68").EntireRow.Hidden = True
Case "4"
Rows("65:68").EntireRow.Hidden = True
Case "6"
Rows("67:68").EntireRow.Hidden = True
End Select

D6 = Sheet11.Cells(11, 7).Value
Select Case D6
Case "0", ""
Rows("60:68").EntireRow.Hidden = True
Case "2"
Rows("63:68").EntireRow.Hidden = True
Case "3"
Rows("64:68").EntireRow.Hidden = True
Case "4"
Rows("65:68").EntireRow.Hidden = True
Case "6"
Rows("67:68").EntireRow.Hidden = True
End Select

D7 = Sheet11.Cells(11, 8).Value
Select Case D7
Case "0", ""
Rows("78:86").EntireRow.Hidden = True
Case "2"
Rows("81:86").EntireRow.Hidden = True
Case "3"
Rows("82:86").EntireRow.Hidden = True
Case "4"
Rows("83:86").EntireRow.Hidden = True
Case "6"
Rows("85:86").EntireRow.Hidden = True
End Select

D8 = Sheet11.Cells(11, 9).Value
Select Case D8
Case "0", ""
Rows("87:95").EntireRow.Hidden = True
Case "2"
Rows("90:95").EntireRow.Hidden = True
Case "3"
Rows("91:95").EntireRow.Hidden = True
Case "4"
Rows("92:95").EntireRow.Hidden = True
Case "6"
Rows("94:95").EntireRow.Hidden = True
End Select

D9 = Sheet11.Cells(11, 10).Value
Select Case D9
Case "0", ""
Rows("96:104").EntireRow.Hidden = True
Case "2"
Rows("99:104").EntireRow.Hidden = True
Case "3"
Rows("101:104").EntireRow.Hidden = True
Case "4"
Rows("101:104").EntireRow.Hidden = True
Case "6"
Rows("103:104").EntireRow.Hidden = True
End Select

D10 = Sheet11.Cells(11, 11).Value
Select Case D10
Case "0", ""
Rows("105:113").EntireRow.Hidden = True
Case "2"
Rows("108:113").EntireRow.Hidden = True
Case "3"
Rows("109:113").EntireRow.Hidden = True
Case "4"
Rows("110:113").EntireRow.Hidden = True
Case "6"
Rows("112:113").EntireRow.Hidden = True
End Select

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Only cases D1, D2, D5, D7, D8 & D10 are running.

All target values are zero so all cases should be running. I have been staring at this for hours and rewritten a number of times, but can't seem to find any problems.

I've picked up VB through forums like this one which have been very helpful, code may not be the cleverest, but does the job I need it to. Hope someone can help?

 

[Andrew Poulsom]

Why have you surrounded your numbers with double quotes? That converts them into a string.

 

[vandango05]

Thank you for your reply.

I've done the same as I have on other worksheets which all work ok. I don't know of any other ways to do it i'm afraid.

 

[Andrew Poulsom]

Well does D3 run if you remove the double quotes?

 

[vandango05]

No I then get a compile error, the cases described in the original post work fine with the "" marks

I've found this on the forum and tried to use it but doesn't run either!

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim c As Range
For Each c In Range("Y10:Y144")
If c.Value = 0 Then
Rows(c.Row).Hidden = True
Else
Rows(c.Row).Hidden = False
End If
Next c
Application.ScreenUpdating = True

End Sub

 

[RoryA]

There is no way you would get a compile error by removing the quotes around "4" for example.

 

[vandango05]

I had removed the wrong ones.

Now changed to this:

D3 = Sheet11.Cells(11, 4).Value
Select Case D3
Case 0, ""
Rows("42:50").EntireRow.Hidden = True
Case 2
Rows("45:50").EntireRow.Hidden = True
Case 3
Rows("46:50").EntireRow.Hidden = True
Case 4
Rows("47:50").EntireRow.Hidden = True
Case 6
Rows("49:50").EntireRow.Hidden = True
End Select

Still doesn't work, confused why D1, D2 etc works but not all

 

[Norie]

Have you checked the values in D3 and the other cells the code doesn't run for?

 

[RoryA]

Hard to say without the workbook but presumably the values do not match your criteria. Try adding a Case Else:

D3 = Sheet11.Cells(11, 4).Value
Select Case D3
Case 0, ""
Rows("42:50").EntireRow.Hidden = True
Case 2
Rows("45:50").EntireRow.Hidden = True
Case 3
Rows("46:50").EntireRow.Hidden = True
Case 4
Rows("47:50").EntireRow.Hidden = True
Case 6
Rows("49:50").EntireRow.Hidden = True
Case Else
msgbox D3 & " does not match any criteria"
End Select

 

[vandango05]

Thank you for all your help, the criteria msgbox helped me narrow it down that the code was linked to the wrong cells. Made the schoolboy error as treating cell K2 as 11,2 not 2,11. Feel very stupid and mad at myself for wasting all this time now.

Thanks again, i'll carry on and try not to make such a silly mistake again!