Using VBA to hide row and row below and VBA to unhide all aswell

[anilsss]

I have a range of Names in cell C19:C420 that I want to rank by sales, I have created a sheet that is showing all my data. Cells in C are merged together, for example, C19 and C20 are merged together to show a persons name. This carry's on up to the end of the data set.

the persons name in column C is derived from an IF statement. if it matches the criteria it will bring in the name, if not it will show "BLANK".

I need a VBA formula to go through the range C19:C420 in the sheet and hide cells with "BLANK" as well as the cell below it and then carry on through the range until it gets to the next blank and does the same until it gets to the end of the range.

Along with this I also need a VBA code to unhide all the hidden cells in that range with a click of a button I'm looking to place in the sheet.

I don't really know anything about writing a VBA code so the help would be grateful.

Thank you in advance.

 

[anilsss]

Thanks Fluff,

I've encountered another problem. I have a subtotal function reading off the data set that is filtered. I want it to sum the values showing and just realised the subtotal function only works on filtered data. Is there a macro that places a filter on the two merged rows? Or a way to sum the range of cells in column C unhidden?

Thank you,

 

[Fluff]

Can't you just use the SUM function?

 

[anilsss]

If I use a sum it will pick up the filtered "blank" cells too.

e.g. if I have the following:
Col B Col C
Person Sales
Joe 200
BLANK 1000
Dave 300
Jason 1200
BLANK 300

if I use a sum formula it will bring back £3,000 even if the (macro) filter is in place and hiding the "BLANK" rows. I need to present the sum of all valid sales people.


Just thought...I could use a SUMIF for everything that is anything but "blank".


I'll see how that fairs up.

 

[Fluff]

Initially you said

I need a VBA formula to go through the range C19:C420 in the sheet and hide cells with "BLANK"

Then in post#11 you said

Or a way to sum the range of cells in column C unhidden

Now it looks like you want to hide rows based on a value in col B, & sum col C. Is that correct?

 

[anilsss]

sorry got the columns wrong there.it was supposed to be columns C and D

 

[Fluff]

Try
=SUBTOTAL(109,your range)