Hide rows macro

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:40 PM GMT

Question

I have anfile where one column is stock ticker symbols. I need to hide all the rows where the ticker symbol is the same as the one above it. Or I could also hide every 251st row, I'm not sure which is easier. I need row 3, 254, 505... Additionally, I would then like to sort the non hidden rows by a value created by the median function. Would sorting the rows change those values and, if so, is there any way around that?

Answer

Duncan

Assume your data starts in A3 and naturally, row 3 would always be visible. Assume that the column with the Ticker symbol is column A.

Sub HideRows() Dim r as Range, cell as Range set r = Range("A4",cells(rows.count,"A").End(xlup)) for each cell in r

 if cell.Value = cell.offset(-1,0) then
   cell.EntireRow.Hidden = True
 else
   cell.EntireRow.Hidden = False
 end if

Next End Sub

I am not sure how you have your median functions set up, but if you sort the data with many hidden rows, your formulas and data will probably become all messed up.

might want to sort by the median first, then hide your rows. There is probably a way to "block" your data and bet the blocks to sort together, but I don't know enough about your data to make a recommendation other than it would involve using the next available column to enter a formula that would identify the blocks and that would probably be part of your sort criteria.

Advertisement

©2017 eLuminary LLC. All rights reserved.