mardi 25 août 2020

Multiple rows hiding in VBA

I have an Excel sheet that I'm trying to put multiple condition to hide rows. 2 of them is hiding some rows if a specific cells equal 0, one of them is hiding some rows if a specific cell has a specific word and lastly hiding rows if there is zero in a specific column. It seems to work when I do separately but I can't seem to make it work jointly. Some of the rows could be included in more than one condition. Please note I'm very novice at that.

Here's what I tried:

Sub Worksheet_Change(ByVal Target As Range)

Dim xRg As Range
Dim R2 As Range
Dim R3 As Range
Dim R1 As Range
    


Set R1 = Union(Rows("61:61"), Rows("68:69"), Rows("72:72"), Rows("91:106"), Rows("117:125"), Rows("144:155"), Rows("157:158"), Rows("164:164"), Rows("166:166"))
Set R2 = Union(Rows("49:52"), Rows("65:129"))
Set R3 = Union(Rows("53:57"), Rows("130:161"))



Application.ScreenUpdating = False

    For Each xRg In Range("E8:E153")
            If xRg.Value = 0 Then
                xRg.EntireRow.Hidden = True
            Else
                xRg.EntireRow.Hidden = False
            End If
        Next xRg


    If Range("E$15").Value = 0 Then
    
       R3.EntireRow.Hidden = True
        Else
        R3.EntireRow.Hidden = False
    End If
      
    If Range("E$14").Value = 0 Then
    
       R2.EntireRow.Hidden = True
        Else
        R2.EntireRow.Hidden = False
    End If
    
        
    If Range("B$3").Value = "USD" Then
        R1.EntireRow.Hidden = True
        Else
        R1.EntireRow.Hidden = False
    End If

        
  Application.ScreenUpdating = True
End Sub

Aucun commentaire:

Enregistrer un commentaire