Hiding rows with merged cells
 

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
--
GNN

Hiding rows with merged cells
 

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

"Obiwaters" wrote:

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
--
GNN

Hiding rows with merged cells
 

it still does the same thing. For the merged rows, only the first row is
hidden.
--
GNN


"cush" wrote:

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

"Obiwaters" wrote:

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
--
GNN

Hiding rows with merged cells
 

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 Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Obiwaters" wrote in message
...
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
--
GNN

Hiding rows with merged cells
 

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

Thanks!!!!!
--
GNN


"Jon Peltier" wrote:

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 Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Obiwaters" wrote in message
...
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
--
GNN