How to Print Only Rows With Data

[Roberta001]

Using Excel 2010 and I need to create a spreadsheet that will be changing weekly...like an order form.

In column A I will have a list of products. In row 1 I will have different sizes available. I only want to print what needs to be ordered. For example: In the workbook I will have separate sheets that will transfer to the main ordering page. Is there a way to set this up permanently so that when I go to print it automatically know to print only the rows that have an order quantity. So in this example it would only print Picture 3, 5 and 7?

Thanks so much for any help

Roberta
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]3x5[/TD]
[TD]4x6[/TD]
[TD]8x10[/TD]
[TD]11x14[/TD]
[/TR]
[TR]
[TD]Picture 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Picture 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Picture 3[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Picture 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Picture 5[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Picture 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Picture 7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Picture 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Picture 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks so much for your help

Roberta

 

[brian.wethington]

VBA to do this might look something like this:

Sub Macro4()'
' Macro4 Macro

Set Rng = Columns("B:E").SpecialCells(xlCellTypeConstants, 23).EntireRow
Rows("1:10").Hidden = True
Rng.EntireRow.Hidden = False
   
End Sub

This could be set up to work prior to printing.. YOu may need to work out the ranges to fit your needs more dynamically as well.

there are many ways to do this actually so if this does not meet your needs, you can describe your needs specifically and I can work you though some other ideas.

 

[Roberta001]

So when I go to VBA.....I insert a module copy and paste the code...but when I do a print preview I am still seeing all the rows....am I missing something?

 

[brian.wethington]

Well you would have to run this code prior to printing.. this could be done in a few ways. You can set it up to run automatically before printing using a workbook_beforeprint event or you can just run it manually before printing. You can run code a few ways for now you can see if it just works by going into the code and pressing F8 to run the code and see if it produces the desired hides. If that works for you and you would like to automate the running of the code we can step through that process.

 

[Roberta001]

Disregard my previous comment...I realized after replying I needed to run the macro but I do have another question....

All product lines will have a similar formula like this =Sheet2!B3 so I can pull from the corresponding sheet. So when there is nothing to order it will have a "0" in that cell. Is there a way for me to print off the rows that has a number greater than zero?

Also...I used Alt F8 to run the code which worked great which hid the rows with no entry...is there a shortcut other than highlighting the page and clicking unhide lines?

 

[brian.wethington]

you can create a macro to undo the hide essentially the following:

Sub UnhideCells()Rows.Hidden = False


End Sub

You can create shortcut keys for these functions if you want as well. To do this you can go to the developer menu in the ribbon, click Macros, find the macro you want to assign a quick key to, click on it and click the options button. in the assign portion ctrl is required and shift and a letter is generally a good quick key combination.

as for your other dilemma, looping through may be a better idea than what I have provided to this point. let me think about it... I really prefer to not loop through things but it can be a quick easy method.

 

[Roberta001]

You are wonderful! Thanks for helping me....

 

[brian.wethington]

So this is kinda' a brute force method. I am sure there is a much more elegant approach.

Sub HideCells()

For x = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
    If Application.Sum(Range(Cells(x, 2), Cells(x, 5))) = 0 Then
        Rows(x).EntireRow.Hidden = True
    End If
Next x


End Sub

 

[Roberta001]

Hi Brian....I put this code in...and your a genius! Thank you so much!!!!