hide rows or columns

[vra]

After filling figures in a table how to hide a row/ column having 0 in a paricular column having a particular heading .. what command or program so that in every worksheet or file it can be used , to avoid it doing manually.Kindly suggest ?

 

[Akashwani]

Hi,

Welcome to MrExcel.

Take a look here to see if this helps....

http://www.mrexcel.com/archive/VBA/8558d.html

I don't write or understand VBA so I'm unable to help you further, sorry.

I'm sure now that you have been bumped back up someone will be able to assist you with amending the code.

Good luck

Ak

 

[vra]

Thanks alot for this. I shall try & get back to u.

Thanks indeed!!

 

[vra]

Actually i want two buttons on sheet : HIDE & UNHIDE ,
which will hide the particular rows ,when range of cells in that row are 0 or blank.
i created button but what code to write & hoe those buttons will work. whether macro will be needed?? etc.etc.

 

[Akashwani]

Hi,

Did you check out the link I gave you?
Did it offer a solution to your problem?
Which Column will contain the 0/blank cells?
What is the range of your data?

Assuming that the link provides you with your answer and that you know how to change the range so that it suits your sheet, try this....

Press alt-F11 to access VBA.
Click Insert-Module.
Paste the code from the link I gave you into the white space on the right.
Paste the code a second time into the same area and change the Sub HideRows () to Sub UnHideRows ().

Change this line in Sub UnHideRows ()....

.Rows(i).EntireRow.Hidden = True

To this....

.Rows(i).EntireRow.Hidden = False

Press alt-Q to go back to your worksheet.

Right click on your Hide button and click Assign Macro, select HideRows from the box, click OK.

Right click on your UnHide button and click Assign Macro, select UnHideRows from the box, click OK.

I hope this helps.

Ak

 

[vra]

Thanks for such a quick reply.On right click no option for assigning macro. M using excel 2007. I want to hide unused rows & columns & then print what is visible on screen. is it possible & how??

 

[Akashwani]

Hey,

How is your workbook saved?
As XLSX, XLSM or 2002-2007 compatible?
If it is saved as XLSX you need to save it as XLSM or 2002-2007 compatible.
You will then be able to assign your macros to the buttons.

You may want to start a new thread for your VBA Print Dynamic Range code

Ak

 

[vra]

i saved as xlsm still on right click no option of assigning macro.
& i do not have option of 2002-2007 compatible, instead i have option of saving as excel 93-2003 workbook.
Secondly can i move this sheet to 2003 excel files?

 

[Akashwani]

Hi,

My mistake that should be 93-2003 compatible.
What did you use to create your button?
I have used a shape and a form control and when I right click the button I get the Assign Macro option.

Ak

 

[vra]

I M REALLY VERY THANKFUL TO TOU!!
I HAVE DONE WITH ONE CHART.
NEXT CHART I HAVE TO HIDE ROWS & COLUMNS BOTH. WILL TRY WITH SAME CODE & THEN GET BACK TO YOU.
VERY KIND OF YOU!! REPLIES ARE VERY QUICK.
FIRST I CREATED BUTTON BY CONTROL TOOL BOX ,, nOW I CREATED AS U SAID.