Macro to hide rows based on defined range in a column

Hi all,

I've done various searches for this but I only seem to be able to find vba code for using values of an entire column.

Essentially, I just need a macro that says "if column B48:60 is 0 or " ", hide rows 248-291.

Also, if there's any way of reversing this to unhide all (because the data might be different each time it is used), then that would be good too.

I don't mind if it runs it automatically or is assignable to a button, I've had limited experience creating very basic macros but this is beyond me.

I hope I've explained this clear enough,and thank you in advance.

Kind regards,

Derry

Hi,

Yes, those cells will always be static and relative to each other.

I will need to expand on it at some point to reflect another range in another column, but I just need the basic macro and presume I can change the ranges as I need to?

thanks

hi again,

There may be data in some or all of those cells, it depends entirely on the source information the user has. I guess it could be easier specified as if B48 = 0 or " ", hide row 252, if B52=0 or " ", then hide row 253,B56 would hide row 254 etc down to B64- row 256

The range in B would be every other cell from B48 to B60, and then rows are 253-256 consecutive. If it helps, the intervening cells in B (B49,B51 etc) are locked and won't have user entry, therefore if it is easier to specify that entire range then it won't cause any problems.

thanks

Derry, this could easily be managed with worksheet event code

Please Login or Register  to view this content.

Thanks Bob,

I've just tried that code but doesn't appear to be working? Do I have to compile or anything before it will take effect?

Arlette, with the cells, to give you a bit of background, The cells from the range in B autopopulate cells in the rows mentioned, ie, B48 populates in Row 258, B52 auto populates in row 259 and so on, that's why I need the rows hidden if there is nothing in the Column B range as the resultant output gets copied and otherwise has massive gaps in it unless the user deletes them all themselves.

For example, B48 and B52 have data, but the rest of the range doesn't, it will then only show rows 258 and 259, and then hide the others.

Thanks

No but it is worksheet event code so it needs to be put in the relevant sheet code module (right-click the sheet tab and select View Code).

To invoke it, enter or delete a value in B48:B60.

Hi Bob,

Sorry but I can't seem to get it to work, I have it in the section you mention but it just doesn't seem to be working

The screen flickers momentarily like it is running a macro but doesn't hide the rows required.

Any ideas?

Thanks for your help with this.

Can you post your workbook?

Hi Bob,

Unfortunately not without clearance from my boss, but by way of update, the macro works-sort of. Rather than than hiding the range specified, it is only hiding the rows relative to B48 and to B60, ie the first and last of the range, the ones relating to the middle always stay shown.

Does this help?

I'll take another look tomorrow, I am off out now.