Condensing IF statements

[sllaksvb]

Hi all, I have a whole list of IF statements and have no idea how to condense the code! Would very much appreciate it if someone could show me how. My code is :



If Sheets("Basic Information").Range("I8").Value = "1" Then
Sheets("Advanced Information").Rows("4:17").EntireRow.Hidden = True
ElseIf Sheets("Basic Information").Range("I8").Value = "2" Then
Sheets("Advanced Information").Rows("4:17").EntireRow.Hidden = False
Sheets("Advanced Information").Rows("5:17").EntireRow.Hidden = True
ElseIf Sheets("Basic Information").Range("I8").Value = "3" Then
Sheets("Advanced Information").Rows("4:17").EntireRow.Hidden = False
Sheets("Advanced Information").Rows("6:17").EntireRow.Hidden = True
ElseIf Sheets("Basic Information").Range("I8").Value = "4" Then
Sheets("Advanced Information").Rows("4:17").EntireRow.Hidden = False
Sheets("Advanced Information").Rows("7:17").EntireRow.Hidden = True
ElseIf Sheets("Basic Information").Range("I8").Value = "5" Then
Sheets("Advanced Information").Rows("4:17").EntireRow.Hidden = False
Sheets("Advanced Information").Rows("8:17").EntireRow.Hidden = True
ElseIf Sheets("Basic Information").Range("I8").Value = "6" Then
Sheets("Advanced Information").Rows("4:17").EntireRow.Hidden = False
Sheets("Advanced Information").Rows("9:17").EntireRow.Hidden = True
ElseIf Sheets("Basic Information").Range("I8").Value = "7" Then
Sheets("Advanced Information").Rows("4:17").EntireRow.Hidden = False
Sheets("Advanced Information").Rows("10:17").EntireRow.Hidden = True
ElseIf Sheets("Basic Information").Range("I8").Value = "8" Then
Sheets("Advanced Information").Rows("4:17").EntireRow.Hidden = False
Sheets("Advanced Information").Rows("11:17").EntireRow.Hidden = True
ElseIf Sheets("Basic Information").Range("I8").Value = "9" Then
Sheets("Advanced Information").Rows("4:17").EntireRow.Hidden = False
Sheets("Advanced Information").Rows("12:17").EntireRow.Hidden = True
ElseIf Sheets("Basic Information").Range("I8").Value = "10" Then
Sheets("Advanced Information").Rows("4:17").EntireRow.Hidden = False
Sheets("Advanced Information").Rows("13:17").EntireRow.Hidden = True
ElseIf Sheets("Basic Information").Range("I8").Value = "11" Then
Sheets("Advanced Information").Rows("4:17").EntireRow.Hidden = False
Sheets("Advanced Information").Rows("14:17").EntireRow.Hidden = True
ElseIf Sheets("Basic Information").Range("I8").Value = "12" Then
Sheets("Advanced Information").Rows("4:17").EntireRow.Hidden = False
Sheets("Advanced Information").Rows("15:17").EntireRow.Hidden = True
ElseIf Sheets("Basic Information").Range("I8").Value = "13" Then
Sheets("Advanced Information").Rows("4:17").EntireRow.Hidden = False
Sheets("Advanced Information").Rows("16:17").EntireRow.Hidden = True
ElseIf Sheets("Basic Information").Range("I8").Value = "14" Then
Sheets("Advanced Information").Rows("4:17").EntireRow.Hidden = False
Sheets("Advanced Information").Rows("17").EntireRow.Hidden = True
ElseIf Sheets("Basic Information").Range("I8").Value = "15" Then
Sheets("Advanced Information").Rows("4:18").EntireRow.Hidden = False
End If


Thank you for your help!!

[p45cal]

Try:
Sub blah()
With Sheets("Advanced Information")
.Rows("4:17").EntireRow.Hidden = False
x = Sheets("Basic Information").Range("I8").Value
Select Case x
Case 1 To 14: .Rows(x + 3 & ":17").EntireRow.Hidden = True
Case 15: .Rows("4:18").EntireRow.Hidden = False
End Select
End With
End Sub
or:

Sheets("Advanced Information").Rows("4:17").EntireRow.Hidden = False
x = Sheets("Basic Information").Range("I8").Value
If x >= 1 And x <= 14 Then Sheets("Advanced Information").Rows(x + 3 & ":17").EntireRow.Hidden = True
or, depending what you want (not) to happen if I8 is outside those values:
x = Sheets("Basic Information").Range("I8").Value
If x >= 1 And x <= 14 Then
Sheets("Advanced Information").Rows("4:17").EntireRow.Hidden = False
Sheets("Advanced Information").Rows(x + 3 & ":17").EntireRow.Hidden = True
End If

[sllaksvb]

Hi p45cal,

Used your first code and implemented it in my macro. Works perfectly! Thank you for your help, really appreciate it.

[snb]

Rows(n).entirerow is 'slightly' redundant.



Sub M_snb()
With Sheets("Advanced Information")
.Rows("4:18").Hidden = False
.Rows(3+.cells(8,9)).resize(14-.cells(8,9)).Hidden = True
End With
End Sub

[p45cal]

Almost.
Sub M_snb()
With Sheets("Advanced Information")
.Rows("4:18").Hidden = False
.Rows(3+sheets("Basic Information").cells(8,9)).resize(14-sheets("Basic Information").cells(8,9)).Hidden = True
End With
End Sub