Hi,
I have a sheet diplays results for KPI's and different sections are seperated by a row. This row provides an aesthetic seperation of the data and also is the row the the contents of merged cells in row A live, so I can't delete them or the contents of these cells disapear.
I will call this row the "seperator row"
I have some code that filters the table to all the KPI's that are not on track to be completed based on the value of cells in column"L".
The problem I am having is that due to the "seperator row" being requried to still be visible once I apply the filter I end up with multiple "seperator rows" visble one after the other for sections that do not have any not on track KPI's to display.
I am trying to apply some code that will hide the duplicate seperator rows on top of each other so that only 1 x row is left to sperate the different visible KPI sections. Each seperator row has an "A" in the cell in column "L".
I have tried to use with offset -1 and SpecialCells(xlCellTypeVisible) to reference the value of the row. But can;t get it to work.
What I want the code to dow is.
If the visible cell in the row above = "A" and the cell referecnce cell in row = "A" then hide the reference row. leaveing onnly the row above the reference cell visible.
This is the code I have tried
<!-- BEGIN TEMPLATE: bbcode_code -->
Code:
<STYLE>.alt2 font {font: 11px monospace !important;color: #333 !important;}</STYLE><!-- END TEMPLATE: bbcode_code -->For i = 16 To 434
Select Case .Range("L" & i).SpecialCells(xlCellTypeVisible).Offset(-1, 0).Value
Case Is = "A"
.Range("L" & i).EntireRow.Hidden = True
Case Is <> "A"
Exit Sub
End Select
Next i
End With
Any ideas on how to get this to work
Thanks
I have a sheet diplays results for KPI's and different sections are seperated by a row. This row provides an aesthetic seperation of the data and also is the row the the contents of merged cells in row A live, so I can't delete them or the contents of these cells disapear.
I will call this row the "seperator row"
I have some code that filters the table to all the KPI's that are not on track to be completed based on the value of cells in column"L".
The problem I am having is that due to the "seperator row" being requried to still be visible once I apply the filter I end up with multiple "seperator rows" visble one after the other for sections that do not have any not on track KPI's to display.
I am trying to apply some code that will hide the duplicate seperator rows on top of each other so that only 1 x row is left to sperate the different visible KPI sections. Each seperator row has an "A" in the cell in column "L".
I have tried to use with offset -1 and SpecialCells(xlCellTypeVisible) to reference the value of the row. But can;t get it to work.
What I want the code to dow is.
If the visible cell in the row above = "A" and the cell referecnce cell in row = "A" then hide the reference row. leaveing onnly the row above the reference cell visible.
This is the code I have tried
<!-- BEGIN TEMPLATE: bbcode_code -->
Code:
<STYLE>.alt2 font {font: 11px monospace !important;color: #333 !important;}</STYLE><!-- END TEMPLATE: bbcode_code -->For i = 16 To 434
Select Case .Range("L" & i).SpecialCells(xlCellTypeVisible).Offset(-1, 0).Value
Case Is = "A"
.Range("L" & i).EntireRow.Hidden = True
Case Is <> "A"
Exit Sub
End Select
Next i
End With
Any ideas on how to get this to work
Thanks
