lundi 10 mai 2021

Optimize if/elsif loop in VBA

I am very noobish in VBA and I would need some help in optimizing a piece of code.

Based on a cell value the code hide / unhide rows in another sheet.

As there are many things to hide / unhide my code is taking too long to run.

It looks like this :

Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("F46").Value = "Hide" Then
            Sheets("Page 3").Rows("06:06").Hidden = True
        ElseIf Range("F46").Value = "Show" Then
            Sheets("Page 3").Rows("06:06").Hidden = False
            End If
    
    If Range("F47").Value = "Hide" Then
            Sheets("Page 3").Rows("07:07").Hidden = True
        ElseIf Range("F47").Value = "Show" Then
            Sheets("Page 3").Rows("07:07").Hidden = False
            End If
    
    If Range("F48").Value = "Hide" Then
            Sheets("Page 3").Rows("08:08").Hidden = True
        ElseIf Range("F48").Value = "Show" Then
            Sheets("Page 3").Rows("08:08").Hidden = False
            End If

And continues for each cells.

There are 9 blocks of cells that hide / unhide rows based on values and for each of these'blocks' there is a link between the cell that hold the data and the rows that is hidden, let me explain :

In the code I posted you can see that, for exemple, when the cell 48 holds the value "Hide" the row 08 is hidden and for the cell 47 the row 07 is hidden. That's what I'm talking about when I write 'blocks'.

Please do not judge me too harsly, I am sure the answer is supper easy but I am not able to resovle my problem without a bit of help.

Aucun commentaire:

Enregistrer un commentaire