CODEWITHSUNDEEP
vbaexcel
os23
os23

Reputation: 145

Un-hiding rows depending on two values

I wrote VBA code to hide the rows in my chart where in the cell of column G the value "XYZ" occurs. 

i = 4 ' row to begin with
j = Cells(i, 7).End(xlDown).Row
clmn = 7 ' column G
mark = False
For row = j To i Step -1
If (Cells(row, clmn).Value = "XYZ") Then
If mark Then
Union(Selection, Rows(row)).Select
Else
Rows(row).Select
mark = True
End If
End If
Next row
Selection.EntireRow.Hidden = True

This works totally fine. Now after working in this modifies chart I'd like to add a second attaching VBA code so that all the hidden rows with "XYZ" in column G reappear BUT only if in column A of the same row there stands "ABC". So not all of the hidden rows should unhide.

How do I have to modify my own code to have this done?

Upvotes: 0

Views: 72

Answers (2)

DisplayName
DisplayName

Reputation: 13386

you could use AutoFilter and go like follows:

Sub UnhideThem()
    Dim visbileRng As Range

    With Range("G3", Cells(Rows.Count, "A").End(xlUp)) ' reference columns A:G from row 3 (headers) down to column A last not empty row
        Set visbileRng = .SpecialCells(xlCellTypeVisible) ' store currently visible range
        .AutoFilter field:=1, Criteria1:="ABC" ' filter referenced range on its first column (i.e. "A") with value "ABC"
        .AutoFilter field:=7, Criteria1:="XYZ" ' filter referenced range on its 7th column (i.e. "A") with value "ABC"
        With .Resize(.Rows.Count - 1).Offset(1) ' reference "data" rows only (i.e skip "header" row)
            If CBool(Application.Subtotal(103, .Cells)) Then Set visbileRng = Union(visbileRng, .SpecialCells(xlCellTypeVisible)) 'if any filtered row in referenced range then add it to 'visbleRng'
        End With
        .Parent.AutoFilterMode = False ' remove filters
        .EntireRow.Hidden = True 'hide all referenced range rows
        visbileRng.EntireRow.Hidden = False ' unhide only rows to set visible
    End With
End Sub

Upvotes: 1

Mahhdy
Mahhdy

Reputation: 592

As Redji comment, just add a condition to your If clause. anyway, I don't prefer using the union and select approach. 

If Cells(row, clmn).Value = "XYZ" and  _
   Cells(row, 1).Value = "ABC" Then
   ' do the selection if you like
Selection.EntireRow.Hidden = false

you also can use the specialcells as well. for instance, if your range is A4:G & j you can address this code in your chart. 

range("a4:g" & j).Rows.SpecialCells(xlCellTypeVisible)

Regards, M

Upvotes: 0

Related Questions