CODEWITHSUNDEEP
vbaexcelmacros
zanzibar
zanzibar

Reputation: 3

VBA macro for hiding rows based on cell value

I am working on a sheet that has sections which hides/shows a number of rows based on a cell value (between 1-10). At the moment, I have a handful of nested if statements. This has made my workbook painfully slow. Is there a way to shrink this code? Thanks.

If Range("B87").Value = 10 Then
        Rows("88:98").EntireRow.Hidden = False
    Else
    If Range("B87").Value = 9 Then
        Rows("98").EntireRow.Hidden = True
        Rows("88:97").EntireRow.Hidden = False
    Else
    If Range("B87").Value = 8 Then
        Rows("97:98").EntireRow.Hidden = True
        Rows("88:96").EntireRow.Hidden = False
    Else
    If Range("B87").Value = 7 Then
        Rows("96:98").EntireRow.Hidden = True
        Rows("88:95").EntireRow.Hidden = False
    Else
    If Range("B87").Value = 6 Then
        Rows("95:98").EntireRow.Hidden = True
        Rows("88:94").EntireRow.Hidden = False
    Else
    If Range("B87").Value = 5 Then
        Rows("94:98").EntireRow.Hidden = True
        Rows("88:93").EntireRow.Hidden = False
    Else
    If Range("B87").Value = 4 Then
        Rows("93:98").EntireRow.Hidden = True
        Rows("88:92").EntireRow.Hidden = False
    Else
    If Range("B87").Value = 3 Then
        Rows("92:98").EntireRow.Hidden = True
        Rows("88:91").EntireRow.Hidden = False
    Else
    If Range("B87").Value = 2 Then
        Rows("91:98").EntireRow.Hidden = True
        Rows("88:90").EntireRow.Hidden = False
    Else
    If Range("B87").Value = 1 Then
        Rows("90:98").EntireRow.Hidden = True
        Rows("88:89").EntireRow.Hidden = False
    Else
    If Range("B87").Value = 0 Then
        Rows("88:98").EntireRow.Hidden = True
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If

Upvotes: 0

Views: 3780

Answers (3)

user6432984
user6432984

Reputation:

You don't need to use EntireRow when using Rows or 'EntireColumnwhen usingColumns`. 

Rows("88:98").Hidden = True

If Range("B87").Value > 0 Then
    Rows(88).Resize(1 + Range("B87").Value).Hidden = False
End If

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23285

You have a whole lot of basically the same code. I took a look and tried to make it more arithmetical, which shortens the code. See if this works:

Sub t()
Dim myVal   As String
Dim mainRow As Long, tweakRow As Long
Dim hideRange As Range, showRange As Range
Dim row1 As Long, row2 As Long

mainRow = 98
myVal = Range("B87").Value

If myVal = 10 Then
    Rows(mainRow - 10 & ":" & mainRow - 10 + myVal).EntireRow.Hidden = False
ElseIf myVal >= 1 And myVal <= 9 Then
    tweakRow = mainRow - 10
    row1 = (mainRow - (9 - myVal))
    row2 = (mainRow - (10 - myVal))
    Set hideRange = Rows(row1 & ":" & mainRow).EntireRow
    Set showRange = Rows(tweakRow & ":" & row2).EntireRow

    Debug.Print "For a value of " & myVal & ", we will hide range: " & hideRange.Address & ", and show range: " & showRange.Address

    hideRange.Hidden = True
    showRange.Hidden = False
ElseIf myVal = 0 Then
    Rows(mainRow - 10 & ":" & mainRow).EntireRow.Hidden = True
End If

End Sub

Upvotes: 1

Andrew Bell
Andrew Bell

Reputation: 29

I might try a case statement.

Oh, or even use the ElseIf option which would reduce the amount of EndIf statements at the very least.

I think the case code looks something like this:

Select Range("B87").value

Case "1"

Case "2"

...

End Select

Upvotes: 1

Related Questions