Printing empty rows

I have a worksheet of 30 rows and 2 columns
There are intermittent rows that have no data
How can I print the worksheet and not have the spaces due to the empty rows.

For example Here is a portion of the follwoing worksheet:

..      A                     B
1     yellow             apples
2     green               pears
3                            oranges
4     blue                 skies
5
6     violet               sunsets
7     black

I would like it to print as follows:
yellow             apples
green               pears
                       oranges
blue                 skies
violet               sunsets
black

Not as follows:
yellow             apples
green               pears
                       oranges
blue                 skies

violet               sunsets
black 


0
livelonger (29)
2/9/2007 6:10:46 PM
excel 39879 articles. 2 followers. Follow

1 Replies
395 Views

Similar Articles

[PageSpeed] 50

You can use a macro to print

Try

This example will loop through row 1:30 in "Sheet1"
If every cell in column A:G is empty it will hide that row.
After the loop it print the sheet and then unhide the rows.

You can also use this with non contiguous ranges Range("B1,D1:G1")
If the cells in column B and D:G are empty it will hide that row.

 
Sub Hide_Print_Unhide()
    Dim rw As Long
    Application.ScreenUpdating = False
 
    With Sheets("Sheet1")
        For rw = 1 To 30
            If Application.WorksheetFunction.CountA( _
               .Cells(rw, 1).Range("A1:G1")) = 0 Then _
               .Rows(rw).Hidden = True
        Next rw
        .PrintOut ' for testing use .PrintPreview
        .Range("A1:A30").EntireRow.Hidden = False
    End With
 
    Application.ScreenUpdating = True
End Sub
 


-- 

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Manuel Murieta" <livelonger@gmail.com> wrote in message news:%23SrelXHTHHA.4188@TK2MSFTNGP06.phx.gbl...
>I have a worksheet of 30 rows and 2 columns
> There are intermittent rows that have no data
> How can I print the worksheet and not have the spaces due to the empty rows.
> 
> For example Here is a portion of the follwoing worksheet:
> 
> .      A                     B
> 1     yellow             apples
> 2     green               pears
> 3                            oranges
> 4     blue                 skies
> 5
> 6     violet               sunsets
> 7     black
> 
> I would like it to print as follows:
> yellow             apples
> green               pears
>                       oranges
> blue                 skies
> violet               sunsets
> black
> 
> Not as follows:
> yellow             apples
> green               pears
>                       oranges
> blue                 skies
> 
> violet               sunsets
> black 
> 
>
0
rondebruin (3790)
2/9/2007 6:17:14 PM
Reply:

Similar Artilces:

Reference every 4th row from a master list of modifiable length
I'm creating an excel database for a clinical study on patients wit migraine. Each patients will treat 4 attacks (episodes). Thi presents a bit of a problem, since there's some information I' collecting that's specific to the patient (demographic information lik sex, height, etc.), and some that is specific to each attack (like th pain level of that attack, the duration of that attack, etc.). T account for this, I decided to give each patient four rows--one fo each attack--and I put their deomographic information in the secon row, leaving the other three blank in those fiel...

How to only print cells where there is data entered, as in a sched
Please does anyone know if it is possible to print selective cells automatically as in schedule employees for a certain day? I have an employee list of about 65 and would like an enlarged printout showing just the scheduled employees each day and their hours. Any ideas? I'm using Excel 2003. I'd rather not have to buy another or add-in program. Cel Have you tried to see what an Autofilter can do for you? Perhaps filter just for that particular day. You can print the results then remove the filter. Gord Dibben Excel MVP On Tue, 19 Apr 2005 13:43:02 -0700, Cel <Cel@discuss...

How do I not print the column letters A-Z but keep the row #'s
I am trying to print a chart where I want to keep the row numbers but not the column headings A-Z printed. Choosing Page setup under the Sheets tab only lists them together not as single choices. Is there a way to customize the sheet to only list the row numbers? How about inserting a new A column and adding =ROW() best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "jgb" <jgb@discussions.microsoft.com> wrote in message news:E503B439-3323-4CE9-A5B8-5477CFC091C2@microsoft.com... >I am trying to print a chart where I want to keep the row ...

hp920c printer prints only the top half of any most lines of text
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Dear computer heros! Sos4help! <br><br>My friend installed the driver for my hp920c and my printer does print - yet only ever the top half of any line of text. The printing result is the same whether I use word or try to print a webpage. If the text is very small, then the printing result is better. Yet, standard font of 12 does not go through. It's not readable. Help! What to do? <br><br>- did my mac software updates <br> - did the utility check <br><br>Yet, how can I verify whether...

Printing hyperlinked Documents
Hello, Quite often, I am sent multiple word documents with multiple hyperlinks to other word documents and pdfs. I am assigned the task of printing out the word documents and the hyperlinked documents in order. I have researched this for hours: How do you print the original word document and all of its hyperlinked documents, in order, without opening up each individual hyperlinked document and printing it? Please keep in mind the fact that I'm not a software guru. I need step-by-step directions. Thank you so much for your help. In news:6B4FF1C0-A684-4E79-B58F-E4A4BAD40E9E@...

Printing Data Dictionary
Hi Everyone, I am using Access 2002 for the first time. I need to print the data dictionary for my database but its giving me a printer error. Error: There was a problem retrieving printer information for this object. The object may have been sent to a printer that is unavailable. I would like to know if instead of printing to a printer can it transfer into a pdf or word document like in 2003. If not can I manually adjust my database to do this? Any suggestions would help! Thanks, Sarita On Tue, 11 Dec 2007 07:56:04 -0800, luscioussarita <luscioussarita@discussions.microsoft....

Troubleshooting double sided printing
I am printing a large publication double sided in Publisher. Due to a paper jam, I need to redo a section of the publication showing both pages, front and back. I don't see any way to "print as a publication" rather than simply a section of individual pages. What version Publisher? If you are using 2003 or earlier, View, uncheck two page spread. On the Publication page, File, Print, current page, click no to the query. 2007 Publisher you will have to type the page numbers, make a note of the mini preview and print preview. You'll discover you will have to ty...

Hiding Rows Conditionally
Is it possible to hide a row only if a certain cell in the row contains a reference to another cell that has a specific value? Here's the scenario: Column A of a Summary sheet contains a list of 20 categories. Each category row contains references to data on another worksheet. Often, there is no data for a specific category, so the Summary sheet displays the category with zeroes or various errors depending on the formulas referencing the data. I would like to hide the entire row if the referenced data cell contains a certain value, for instance "0". Can it be done? Thanks for a...

Combining rows of data that have one cell in common
Hi everyone, let me explain... I'm working with a large excel file (13,000+ rows) of cancer cases recorded in the state of Florida. Each time a patient presents with a cancer, that "case" is recorded as a row on the spreadsheet, along with a UNIQUE patient ID assigned to that particular individual. If that same person develops another cancer sometime later, that information appears in a new case/row, but with the SAME patient ID. I need to export this file to another program (SPSS v13.0) to do some advanced statistical analysis. However, the program requires that all of t...

print white as a color
I am trying to print a program onto a light blue paper... I want to print white as a color... but it keeps coming out as black or nothing at all. anonymous@discussions.microsoft.com wrote: > I am trying to print a program onto a light blue paper... > I want to print white as a color... but it keeps coming > out as black or nothing at all. ========================================= Depends on your printer...inkjets don't print in white. (at least none that I am familiar with) If you have a background color in your project (light blue for example) you could choose white text...bu...

Printing the first two rows as headers.
I would like to have the first two rows print at the top of every page of my print out. I know this is a basic function, yet I can not seem to find it in the help function. Could someone please post how to do this /AND/ tell me what key words to put in the search/help function that would show the answer as well. Thanks Michael -- J. Michael Morse michael@stignatius.cc <mailto:michael@stignatius.cc> St. Ignatius <C:/Parish%20Administration/Graphics/Bulletin/Ignatius2C.jpg> "Let no one ever come to you without leaving better and happier." - Mother Teresa Hi ...

vb dataset row count incorrect
Hi Has anyone else had this problem. Im using a vb .net dataset to read & write XML but when I check the number of rows for a table it's incorrect. Example number of rows = 4 table row count says there are 8 rows does anyone know how to correct this problem ? Regards Neil neil rowe wrote: > Has anyone else had this problem. Im using a vb .net dataset to read & > write XML but when I check the number of rows for a table it's incorrect. > > Example number of rows = 4 > table row count says there are 8 rows That may have something to do with whitespa...

Can I print report and then delete at the same time?
I want to print the info from a sub report and have the info deleted at the same time OK. I'll bite on this troll bait ... Why? Rob Norm wrote: > I want to print the info from a sub report and have the info deleted > at the same time Define "deleted"... -- Regards Jeff Boyce Microsoft Access MVP Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offer...

Outlook 2007 Crashes when trying to print Calendar
Hi, I used to be able to successfully print the month view of my calendar, but lately it crashes MS Outlook every time I try to print it. I can still print Day or Week calendar views as well as Month calendar using a list view as opposed to the normal cakendar view. I am running Windows 7 Evaluation copy & it doesn't seem to matter which printer I have set as the default, it still crashes. I have uninstalled & re-installed MS Office, but the problem still persists. Any help / suggestions would be much appreciated. Thanks & Regards, Stephen What error ...

How do I get '&' to print within the Header of a Excel Worksheet?
I am having trouble getting the & to show up in the Header of an Excel Spreadsheet. What am I doing wrong? For example: Smith & Smith Use two ampersands. The first one gets eaten by Excel. Francis Hayes (The Excel Addict) http://www.TheExcelAddict.com mmmmmmmm ampersands.... "ExcelNut" wrote: > Use two ampersands. The first one gets eaten by Excel. > > Francis Hayes (The Excel Addict) > http://www.TheExcelAddict.com > . > ...

How to Show/Hide Columns or Rows with no data via VB
Hi, I am need of some advice please. I have a spreedsheet (2007) which I designed as an attendence register. Currently it works well but I want to improve on a few matters. For the main page I have a column with the date, next column day, then the next 40 columns are set for the crew employees. These are broken into 4 groups of 10 for each crew (A,B,C,D, Shift workers), however there is normally only 7 people per crew, the other 3 are blank unless there are extra enployees to that crew. Currently I am hiding and showing manually, but I want to be able to do something via ...

Jpeg pictures print as solid black boxes.
I have created a catalogue in Publisher with .jpeg pictures inserted from a file, some are okay but most are printing as solid black boxes. The print preview shows them perfectly. On Thu, 2 Mar 2006 03:07:27 +0000, Majick wrote (in article <045C4C06-9DC4-4713-B1C2-3AC4C3D2A348@microsoft.com>): > I have created a catalogue in Publisher with .jpeg pictures inserted from a > file, some are okay but most are printing as solid black boxes. The print > preview shows them perfectly. Nothing whatsoever to do with Publisher. Update your printer driver. Majick wrote: > I h...

Background Printing
Can you print a background picture inserted in Excel? Hi Have a look at this link: http://groups-beta.google.com/group/microsoft.public.excel.misc/browse_frm/thread/7e28ad74f96710a8/57c7658eb78bf1ef?q=watermark&rnum=1&hl=en#57c7658eb78bf1ef -- Andy. "Piet" <Piet@discussions.microsoft.com> wrote in message news:4C970CDF-CACC-4A79-8C13-4E1BEDA2C49C@microsoft.com... > Can you print a background picture inserted in Excel? ...

printing individual envelopes
Does anyone have an idea on how to quickly print an individual envelope from a Account or Contact record in MSCRM 3.0? On May 21, 6:29=A0am, X-eqt <X...@discussions.microsoft.com> wrote: > Does anyone have an idea on how to quickly print an individual envelope fr= om > a Account or Contact record in MSCRM 3.0? =A0 Mail merge is limited in the fields it can access in version 3 but the quickest way would probably be to run mail merge or a quick campaign with a template set up as you need it. Another option would be to set up a report to do the trick or finally export to Excel an...

Dos printing on network #2
Hello I am using windows printing technology i.e( calling Onprint,OnBegingPrinting functions)but instead of calling draw text to print on printer I am writing the data in the file i.e fprintf for printing contents. I have to use the printing functions because of some logic that depends on windows printing loop. After creating the file I prints this file using DOS printing. i.e copying this file to LPT port. This gives me fast printing. But because I am using windows printing functions i.e OnPrint... the new printing entry is created in printer spool . 1.Windows file with blank contents -- st...

Publisher and HP 6540 borderless printing
Hi, I've got an HP 6540xi. I am trying to print a full page 8.5 x 11 landscape brochure in borderless mode that I've created in Publisher. When I print it, however, it stretches the image by about 2% (about .25 in) in the lengthwise direction so the image ends up being chopped on one end. This problem occurs both in Powerpoint and Publisher. I tried printing an 8.5 x 11 photo in the HP Photo utility that came with the printer, and it works fine from there. Is there any way to get the Microsoft products to print to this device correctly? Thanks, Thomas This is what HP say...

insert a row in a protected sheet
hello out there, does anyone know in excel. how do insert a row in a protected sheet without unprotecting the sheet. i have a document where i do not want the formula to be touched in no way form or fashion but i would like my users to constantly add or delete information as they occur and the only way to do this is by inserting or deleting a row to enter information. thanking you all in advance for your continued help. thank you, rennoyinc ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! Free Support at http://www.ozgrid.com/forum/ ** Which versio...

Conditional Formatting Whole Row
Morning All, Using Windows and Excel XP. I have a worksheet that has the dates of this month in A1:A31 and other data in B1:F31. I have set up a conditonal format: cell value equal to =TODAY() to have the current date (December 6) in A1:A31 to highlight in yellow. What I would like to do is not only highlight the current date but also all the data in that particular row. For example, in A1:A31, December 6, 2003 (cell A6) is highlighted in yellow. How can I get the entire row (A6:F6) to be highlighted in yellow. Is it possible to do that? Thanks in advance, Mi...

Freeze title row before sort
How do I sort a column without moving the title row? Click in the column to want to sort. Click on Data, Sort. Make sure there is a checkmark in Header Row at the bottom of the dialogue box. Is this what you want to do? -- Rae Drysdale "Rich R." wrote: > How do I sort a column without moving the title row? ...

Adding next row of formulas
I have Excel 2003 at work and want it to add the next row of formulas when enter is pressed on the last column that contains a formula. Is there an easy way to do this? At present I have a bunch of rows and columns with formulas and no data. I used sumproduct to calculate totals based on criteria and I think I can reduce the calculate time by adding dynamic ranges. This workbook is used by 2 of us and the other person has even less experience than me. Thanks, Lee > when enter is pressed Pick a different hotkey and write a macro to copy the formulas. You could even have the ma...