Help!! Inserting Rows in Excel using VBA

[000]

I am creating a spreadsheet that will end up having atleast 200 rows when completed. I have my spreadsheet broken up into different sections and each row has a cell that will either display Yes or No.

My VBA code is a bunch of If and ElseIf statements that will either display the entire row when the cell reads "Yes" or it will hide the entire row when the cell displays "No".

My concern is if I forgot to add something in a section near the top of the spreadsheet, I would have to insert an entire row. All of the cells called out in each line of VBA code would then be one off.

Is there any way of still being able to look at whether each cell says yes or no and hiding that row accordingly? I just do not want to have to insert a row and then have to go and change all the cell values in each line of code below where I made the insert.

Thanks.

 

[000]

Would there happen to be any way of having this program only look at the cells over to column E?

 

[MrKowz]

Would there happen to be any way of having this program only look at the cells over to column E?

Try:

Public Sub HideNo()
Dim i   As Long, _
    j   As Long, _
    LR  As Long
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
LR = Range("A" & Rows.Count).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To LR
    Application.StatusBar = "Currently checking row " & i & " of " & LR
    For j = 1 To 5
        If Cells(i, j).Value = "No" Then
            Rows(i).Hidden = True
            Exit For
        End If
        If j = LC Then
            Rows(i).Hidden = False
        End If
    Next j
Next i
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .StatusBar = False
End With
End Sub

Again, it uses column A to determine what the last row of the data is.