Hide rows based on value of SpecialCells(xlCellTypeVisible) in row above

Bradmc

New Member
Joined
Jul 27, 2010
Messages
1
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,553
Messages
5,832,436
Members
430,133
Latest member
gerasimos

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top