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.

 

[Gary McMaster]

You could use named ranges and refer to the ranges by name in your code.

Gary

 

[MrKowz]

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.

Welcome to the forums!

Why not instead just use a filter?

 

[000]

This is my second time using vba. I am not very good at programming. How exactly would I use a filter?

 

[MrKowz]

Filtering is a non-vba solution. Select your range of data, then go to Data>Filter>Autofilter. This will give you drop-boxes on all of your headers. Choose the header you want to filter the "No"s out of, and un-tick "No". This will hide all rows with a "No" in that column.

 

[000]

As it is right now I have a command button that I use to update the spreadsheet. I do not have column headers because the cells that display the "yes or no" are not all in the same column so I do not think filtering is going to work.

Is there some way to loop through each row and tell the program to look through each cell in the row to see if there is a NO somewhere and then hide that entire row?

 

[MrKowz]

Try this code out. If a "No" is found in a row, it will hide it. If a "No" is not found, it will unhide the row. Note that Column A is used to determine what the last row is, and row 1 is used to determine what the last column is.

Public Sub HideNo()
Dim i   As Long, _
    j   As Long, _
    LR  As Long, _
    LC  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 LC
        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

 

[000]

It doesnt seem to be working. Some of the cells that have the yes or no start in column b,c,and d. Would this make a difference?

 

[000]

Nevermind....it is working now that I changed the No in the code to the way it is formatted in the spreadsheet. Thank you so much!

 

[MrKowz]

It would not.

Do you have data in column A that goes down the the bottom of your table, and data in row 1 that goes out to the right side of your table?

I just tested it on my end and it works fine.