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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Watch MrExcel Video

Forum statistics

Threads
1,123,078
Messages
5,599,632
Members
414,326
Latest member
kfg1287

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