Hiding rows with merged cells

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
 
C

cush

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
 
J

Jon Peltier

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
 
O

Obiwaters

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

Thanks!!!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top