Hide blank rows for printing

[knucklehead]

I have an Excel workbook template that contains several sheets that is linked to a single sheet that I use for a report. The information in the worksheets will change for each individual that I have to do the report on, so that some rows will be blank. I have tried to figure a way to only link the cells with text in them, so that if sheet 1, cell b10 was blank it would automatically go to sheet 2, cell b10, etc. but was unable to figure that out. Now what I thought I could do, is go ahead and link all of the data together in the sheet for my report, then automatically hide any rows that were blank, so that I could fit all of the information onto one page when the report is printed. For example, in sheet 5, I have links to sheet 1, cells b10:b20 in sheet 5, cells a5 to a15. So, if cells b15 to b20 on sheet 1 is blank, I would want cells a10 to a15 to be hidden so that when I print, there isn't a big blank area in my report. I am not familiar at all with macros, or vba, so if there is a formula I could use to accomplish this, that would be wonderful! I would be more than willing to send the template to anyone who thinks they may be able to help. I have been working on this for several weeks, and need to get it fixed ASAP! Thanks in advance for any help anyone can provide!

 

[steve case]

Try this one:

________________________________
Sub CopyVisibleOnly()
'
' CopyVisibleOnly Macro
' Macro recorded 3/27/03 by Please Enter Your Name Here
'
' Keyboard Shortcut: Ctrl+Shift+C
'
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
End Sub
_________________________________

Make your selection to copy and press [Ctrl]+[Shift]+[C]

The hidden columns and rows don't get copied

Then paste.



Feature is found under [Edit] [Go To] [Special...] [Visable Cells Only]

 

[knucklehead]

Thanks for the info, but can you tell me how to record a macro? I am completely clueless about how to do this. I tried to do one once, but could not get it to work. If you could tell me how to do this, I would appreciate it very much. However, you would have to treat me as if I were a complete moron (as opposed to an incomplete moron) as I know nothing about macros. The best I can do right now is do some simple formulas.

Thanks so much!

Karen

 

[steve case]

Karen

I hope I didn't leave any really important little clicks and buttons out of this:


Click on [Tools]
Click on [Macro]
Click on [Record New Macro] and don't do another thing!

The [Record Macro] dialog box should have appeared.
Don't click [OK] until I tell you
Under [Macro name:] Name your macro: "SkipHiddenCells" or "Suzy" or whatever you would like, but as a habit, you should ALWAYS name your macro.

Under [Store macro in:] select Personal Macro Workbook
Now a word of caution here, most times you will want "This workbook" What's going to happen is the next time you go to record a macro, the Personal Macro workbook will be the default.

(AS I think about this, maybe you want it in "This Workbook" if other people are going to use this. If it's just for your machine, stay with the Personal)

Next click in the box next to [Ctrl+]
Press [Shift]+[C] The dialog box should now say [Ctrl+Shift+] and [C] should be in the the box.

I always skip [Description] It's up to you


Now when you click on OK, keep in mind what's going to happen, you'll be recording your key strokes. I suggest you click on a cell [A-1] and then click on the "Stop Recording Dialog" box which should have appeared. If it didn't, don't panic, Select [Tools] [Macro] [Stop Recording]

Alright, here we go: Click on "OK"

Click on cell [A-1]

Click on Stop Recording like I told you.

Make sure you stopped recording! You should see "Record New Macro" when you select [Tools] [Macro]

OK, here comes the fun stuff:

Press [Alt]+[F11]

Microsoft Visual Basic (VBA) should come up.

Find the folder named Module 1 under VBAProject(Personl.xls) and double click on it.

You should now see your macro in the right hand frame.

Find the code: Range("A1").Select

Get ready to cut&Paste

Copy the following code to the clipboard:

________________________________________________

Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

________________________________________________

Hi-light just the code "Range("A1").Select"

Paste by pressing [Ctrl]+[V]

YOU'RE DONE!



Well sort of. Close the Mcrosoft Visual Basic (VBA) window.

Select [File] [Exit]
A dialog box will ask you if you want to save your personal macro. Say yes.

Reload Excel and your favorite file and try it.


If you did it correctly when you select cells that span hidden columns & rows you don't want to paste, Press [Ctrl]+[Shift]+[C] instead of the usual [Ctrl]+[C] to copy. Then paste [Ctrl]+[V] into your destination cell.

Did it work?

Steve Case
(No relation to that AOL guy)
stacase@hotmail.com

 

[knucklehead]

Steve,

I don't think I am doing this correctly. Do I copy over the code "Range("A1").Select"? Because that's what I did when I highlighted it then pasted in the code that you gave me. However, when I went to exit out of it, no dialog box appeared asking me if I wanted to save it. Then when I opened the file and tried it, it just copied the information that was in cell A1. Also, the cells that I don't want printed are not hidden yet, I need to hide them before printing (see below).

The file that I am trying to do this in is going to be a template for other people to use and the information (blank cells) will be changing for each file that they create. The cells I have are B10:B49 on each of three different sheets. Not all of the cells on each sheet will have information in them. Some of them will be blank, based on the individual file. So what I need to do is when information is put into these cells, it needs to automatically update on another sheet that is going to be a report of all of the information from all three sheets combined (I already have a formula for this on the report sheet). However, when I print the report, I want all of the information from all three pages to run continuously, and not to have big gaps from the blank cells on the other pages (if this makes sense). The other individuals who are using this have the most basic knowledge of Excel, so I am trying to make this as simple for them as possible, so that they don't have to copy and paste each time they do these forms.

I will send you the file if you would like to look at it. I have tried to download Colo's HTML maker so that I could post files on the board, but couldn't get that to work either. I do want to learn how to do this though, so if you could just look at it and tell me how to do it, then maybe I can learn to do other macros as well.

I appreciate the help tremendously. You guys have been a big help to me, and I have learned a great deal from this forum. I think this a wonderful source of information for new Excel users as well as those with more experience. I look forward to hearing from you soon.

Thank you again for your help!!!!!!!!!!

Karen

 

[Barry Katcher]

Hi, knucks. Take a look at this; no code is needed. Look at the formulas in column A. They return either "hide row" or "print", depending on whether there is a zero in column E. Then, before you print, you just do an Data/AutoFilter on column A, choose "print", and it will hide all the zero rows. After printing, just unhide the rows. You can record a simple macro to do all of this if you want, but it's not even necessary.

Book1
	A	B	C	D	E
1		DEPT.	Widget1	Widget2	Total
2	print	Dept1	20	30	50
3	hiderow	Dept2	0	0	0
4	print	Dept3	50	60	110
5	hiderow	Dept4	0	0	0
6	print	Dept5	80	90	170
Sheet1

 

[steve case]

"Do I copy over the code "Range("A1").Select"?"

Yes

"However, when I went to exit out of it, no dialog box appeared asking me if I wanted to save it."

If you put it in the personal file it should have appeared. If you put it in a workbook file it would have been saved with your normal save. The dialog box only appears when you exit from Excel, not when you just exit from the file.



"Then when I opened the file and tried it, it just copied the information that was in cell A1.

Hmmmmmmm


"Also, the cells that I don't want printed are not hidden yet,


The macro I put up is only for when they've already been hidden.


"I need to hide them before printing (see below)."


Sorry if I put you through some frustration.

 

[knucklehead]

Steve,

You are a genius! Thank you, thank you, thank you! This worked like a charm. I have been banging my head about this for weeks! We were supposed to begin using this by the first of July, but I just couldn't get it figured out. You will never know how much I appreciate your help and your patience. Again, THANK YOU!!!!!!!!!!!!!!!!!

Karen - A stooge fan

 

[steve case]

Karen

Well you're very welcome and my ego has needed boost for the day. Just remember, if you put it in your personal, it will only work on your machine, but on all files. If you put in the workbook file, it will work an all machine, but just that file.

Steve