Hiding rows with merged cells

  • Thread starter Thread starter Obiwaters
  • Start date Start date
O

Obiwaters

Hi,
I am trying to hide rows based on the values in their cells. all these cells
are in column "L" and if the value of the cell is "NA" then i want to hide
the entire row. the problems is that some of the rows and therefore cells are
merged and i cant figure out how to hide the whole merged row.
From what i have, only some of the rows in the merged rows are hidden.

here's what i have:
With Range("L1:L400")

Application.ScreenUpdating = False

For i = 1 To .Rows.Count
If Cells(i, 12).Value = "NA" Then
.Rows(i).EntireRow.Hidden = True
Else
.Rows(i).EntireRow.Hidden = False
End If
Next i

Application.ScreenUpdating = True

I also tried:

Dim myAddress As Variant
Dim myCell As Range
Dim iCtr As Long

myAddress = Array("L1:L400")

With Worksheets("CTVDOrderDataMapping")
For iCtr = LBound(myAddress) To UBound(myAddress)
For Each myCell In .Range(myAddress(iCtr)).Cells
myCell.EntireRow.Hidden = (myCell.Value = "NA")
Next myCell
Next iCtr
End With

I still have the same problem where only some of the rows in the mreged rows
are hidden.

Please help
 
Does this work for you:

Sub Hide_NA_Rows()
Dim i As Integer
For i = 400 To 1 Step -1
If Range("L" & i) = "N/A" Then
Range("L" & i).EntireRow.Hidden = True
End If
Next i

End Sub
 
Merged cells are evil, and should be avoided if possible.

When not possible, you can revert to other tricks. First, loop from the
bottom row to the top, and use this:

Cells(i, 12).MergeArea.EntireRow.Hidden=True

- Jon
 
Jon, I agree...Merged Cells are EVIL. I played around with the line you gave
me and IT WORKED!

Thanks!!!!!
 
Back
Top