Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Too Many Rows And Columns

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Apparently someone highlighted the whole worksheet when creating cell outlines, so we have HUNDREDS of pages of just blank cells when printing (we caught it before too many sheets were printed.). I ended up just copying the context of the worksheet onto another worksheet in the same book. I was just wondering how I could delete the cells without doing this step.


Similar Excel Video Tutorials

Helpful Excel Macros

Print Specific Pages in Excel
- This free Excel macro allows you to print a pre-specified selection of pages from Excel. This means you can print 2 pag
Delete Rows in Excel if Completely Empty
- This macro will delete only completely blank rows in an excel spreadsheet. It allows you to make a selection of rows, ru
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Delete Blank Rows in Excel
- This is a macro which will delete blank rows in excel. This version will delete an entire row if there is a blank cell
Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
- Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the

Similar Topics







Okay here's my problem. I have a worksheet that has the print area set up to print multiple pages. When I hide the rows that make up a page that I don't want to print (Say pages 4 and 7 out of 10) I end up with all ten pages printing, but the pages that I didn't want to print show as blank pages. Any why that anyone knows of printing the worksheet (all at once) without deleting those rows or ending up with blank sheets would be a great help!
thanks


Hello,

I have a worksheet with 20 pages in it. Each page is actually a report. I need a macro which will test to see which reports have been done, and then print only those pages.

For example, there has been a report entered in page 1, 2 and 4, but not 3. So I want the macro to print pages 1,2 and 4, but not 3.

For the purposes of the macro, I can have a range of cells, say for argument's sake, A1:A20, with either 1 or 0 in them depending on whether the page is to be printed or not. If page 1 is to be printed, A1 will have the value 1 in it; A2 will have 1 if page 2 is to be printed; A3 will have 0 in it and therefore page 3 will not be printed, and finally A4 will have 1 in it and page 4 will be printed.

I thought I would easily be able to find something about this online, but I've only found references to printing entire worksheets, not specific pages within a worksheet.

Thank you very kindly to anyone who is able to help me with this.

Nicholas Stone

im creating a Job sheet each work book will have all the jobs for that month

what im trying to do is have a master worksheet called master that is blank,

then whe i finish the jobsheet click a button and it prints that sheet, locks ALL the cells, copys the master sheet and gives it a new number witch is basically +1 of the one ive just printed, and renames the tab with that number.

the printing, locking the cells and copying the worksheet is fine thats not a problem, its making the job number +1 of the previous job sheet automatically with out any human input. ive cheated with the tabs where when you copy a tab its adds Job (3) in brackets and im using that for my job number in the tabs


Apologies if this has already been answered somewhere but I've searched for ages and can't find an answer.

I have a worksheet that when printed prints out 24 pages, excel does not allow me to print specific pages i.e. 3,5,7,9,11 only ranges i.e. 3-11. How can I overcome this, ideally I would like to have a button linked to a macro that will print the pages according to a cells content. ie. if the cell contained 5 it would print page 5.
I wonder if this could be taken a stage further to enable a number of pages to be printed out in one button push i.e. a range of cells containing numbers 3,5,9,11 to print out those pages.


I have a workbook with twenty-four sheets, each one representing a week of a roster.

Any uncovered shifts are highlighted in yellow and the text is bold. I would like to automatically look through the roster for any yellow cells and then copy the details of the shift onto new worksheet.

I have the code for creating the worksheet etc, I was just wondering what's the most efficient way of searching through every cell in each worksheet.

I realise this may take some time, but it is quicker and easier than printing them out, then going through them all with a highlighter!

For an added complication, each worksheet has a start and end date in cells c2 and g2 respectively. Ideally, I would only count sheets where today's date is before the date in c2 or after the date in c2, but before that in g2.

Thanks
Chris


I am creating a table in one worksheet (Sheet3!) which references cells in another worksheet (Sheet1!) in the same book (called template.xls)
Now, I have 60 other books, in which I want to do the same thing. I want to copy this sheet3! as a model, however, when I try copying the sheet and pasting it in one of the new books (book1), the reference is copied as [template.xls]Sheet1!, thus the data is referenced from Sheet1! in workbook template and not from Sheet1! of book1.
Is there any labor saving way of copying the cell reference to a different worksheet in the same book into a new book so that it now references a parallel worksheet in the new book?
i.e. can relative cell references be made to apply to worksheets also, or is only for rows and columns?

PS - I work with excel 2003


i have a worksheet in which cell N10 counts the number of cells in
column D that have entries, and using an if statement, determines how
many pages need to be printed in order to print all rows that have
entries. The cell displays 1, 2, or 3 [pages], depending on the number
of rows that are filled in. For example, if the count is greater than
62, 2 pages have to be printed. If less than 62, only one page need be
printed. I have to do this because there are formulas in other rows
that go down all the way to the 169th row and 3 pages will be printed
out if i dont specify in the print range the number of pages i need
printed. Is it possible to have a macro use the cell value of 1 or 2 or
3 and then print only the matching required number of pages.

thanks

Tonso




Hello,


I am inputting data for work...

This might be a little complicated...

I have on program per worksheet (6 worksheets total).
In each worksheet I have different categories with data. (every 5 rows is a different category).
I only want 2 columns to print from each worksheet (the subheadings for each row, and then the data).

If I printed what I want out normally, I would get 3 pages of 2 columns times 6 worksheets. These 2 columns do not fill up the whole page, and I would like to be able to print all 3 of these "pages" of columns on just one page.


Is there a way to print like this?

(basically print one long column broken up into 2 columns to be printed on one page)
The only way I could think of to do this, was to create a whole new worksheet, and have it refer to the data in the way that I wanted... but that seems like way too much trouble. There must be a way I can manipulate the page breaks to do this?


Is there a way to change the cell that is highlighted when a worksheet is first opened? Someone created a worksheet for me to use a while back, and I ended up having to tinker around with the layout a bit.... and now the box that is highlighted when I open the worksheet is 2 or 3 cells too high, I need it to highlight a lower cell so I can start inputting my data as soon as the worksheet is opened, instead of having to click to the correct box first.

Any help would be GREATLY appreciated!


Hi folks,

Is it possible to print my worksheet without colour. ie white backgrounds to all cells.

I have created a worksheet that will be completed by folks who will need to fax it occasionally. On screen I want it to retain its colours as they indicate what should be complete din each individual case. However, when its printed it will be easier to fax with no backgrounds to any cells and black outlines/fonts etc.

How can this be achieved (if at all possible?)?

thanks

Dale


I see in the Setup - Sheet's Tab you can select Row and column headings to be
printed, but that prints the litteral headings such as A, B, C and 1, 2, 3

I need for have the content that I have named my columns reprinted on the
top of each page so that users of my printed spreadsheet don't have to flip
back to page 1 see what the titile of a certain column is to understand the
data in a certain cell in page 3, etc. of the worksheet in printed format.

I'm familiar with the freeze pane issue so you can see the title cells when
scrolling the worksheet - that's not my issue - I need the top row to PRINT
on all pages of a mulitple page worksheet.



Hi there,

I have a bit of a problem and I have no idea, yet, how to solve it. Here is my problem. I have a worksheet, which I want to use as a form. This Worksheet contains 6 blocks. Block 1 and 2 contain static data. Block 3 is optionally visible by clicking a radio button in block 2 (macro programmed). Block 4 is always visible. Block 5 is also optionally visible by clicking a radio button in block2 and block 6 is also always visible (static data). Block 3,4 and 5 will contain dynamic data. Now here's the challenge.

When printing I see 3 pages. Page 1 seems normal. Page 2 and 3 show data from page 1 added by data from page 2 and 3?? I have already tried to set page breaks (no luck). I tried dynamic printing, but this obviously only counts for pages with dynamic data.

What I want? If block 3 and 5 are selected (single or simultaniously) the data within the cells must be printed. Rows which do not contain any data must not be printed. If rows are added these must be printed also, in case of adding more data then rows.

How can I realize this? Is it also possible to distribute the data evenly on the pages to be printed? I can sent the sheet in question if that makes it more understandable.

Please excuse me for not going through all the threads, I simply haven't got the time at this moment. I would be very happy if someone could come up with an answer.

Thanks in advance,

Marco (Holland)


Hi,

Is there anyway to make something appear only during printing and not appear during the worksheet (Using macros or otherwise) for example lets say i have an open ended sheet that displays some transactions , and i want when i print the last row to always have the Totals, but when im not printing i dont want to see it there or else they'll be no space to add data
I know i can just do this manually but it would really help to automate it..
Also, is there anyway to make something appear at the top of each page (printed page)
Thanks!


I need some help creating macro for copying 3 columns from sheet1 (workbook1) to workbook2 and 3 columns from sheet2 (workbook1) workbook2 and it should paste from where the data from sheet1 (workbook1) ended and i will have to do this from 3 more different sheets from workbook1.

I will be doing this everyday so i want to make sure it the pasting gets done from where the previous copied data ended last.

Any help would be appreciated.


I am creating several worksheet where the first two rows are repeated so that I can see those two rows as I proceed down the sheet. That feature works on some but not all sheets. What am I missing? I even tried copying the pages that are correct to another worksheet and that format doesn't copy to the next worksheet. This has to be a simple fix, yes????? Any input would be GREATLY appreciated.


Hello Guys,
Need your help again.

I am trying to figure out how to hide rows automatically when 1 row or more rows have zero (0) values and/or blank.
Have attached here a working file for reference.

What I'm doing now is entering data in Sheet1(a) and/or Sheet2(a). Data here are linked to the printed sheet-Sheet1 and Sheet2.
Every month I will be printing this printed sheets numbering 10 or more and I will be adding more sheets to be printed in the future.
Before printing, I have to hide row/s having all 0 values across calculated, for all the 10 or more printed sheets. (e.g. In Sheet1, rows 10, 16, 19, 23 and 37). So this takes time.
Note that the columns in the printed sheet will probably remain the same.

For this, is it possible as well for a toggle button to be utilized for all the codes in the printed sheets to run-a. hide the rows with 0s when toggle on and unhide the rows when toggle off. Likewise, the caption will change between toggle.

This toggle button is placed in a summary sheet, but for this purpose, is placed in 1 - worksheet.

Can you guys help me code this?

Thanks in advance for the great help.


Hello again Guys,
Need your help again.

I am trying to figure out how to hide rows automatically when 1 row or more rows have zero (0) values and/or blank.
Have attached here a working file for reference.

What I'm doing now is entering data in Sheet1(a) and/or Sheet2(a). Data here are linked to the printed sheet-Sheet1 and Sheet2.
Every month I will be printing this printed sheets numbering 10 or more and I will be adding more sheets to be printed in the future.
Before printing, I have to hide row/s having all 0 values across calculated, for all the 10 or more printed sheets. (e.g. In Sheet1, rows 10, 16, 19, 23 and 37). So this takes time.
Note that the columns in the printed sheet will probably remain the same.

For this, is it possible as well for a toggle button to be utilized for all the codes in the printed sheets to run-a. hide the rows with 0s when toggle on and unhide the rows when toggle off. Likewise, the caption will change between toggle.

This toggle button is placed in a summary sheet, but for this purpose, is placed in 1 - worksheet.

Can you guys help me code this?

Thanks in advance for the great help.


I have a work book oringinally created in Excel 2003 that has 12 worksheets, some containing 1 page, some with 3 or 4 pages. I am now using Excel 2010 and have the following problem: When I try to print the entire workbook (total of 30 pages)
as one file collated and stapled, it collates and staples the 3 sheets from one worksheet, 2 sheets from another, etc. I need all 30 pages printed in order, and then stapled as one set. Any thoughts?


Hello all,
I have a spreadsheet which I use to log tickets. It has a few hundred rows and about ten columns. I have printed it before with no problems, I have the row heading repeated on every page.

Something odd has happened though, I am not sure what I did to mess it up, but when I print, the first page is only printing partial information.

When it printed, it did the header and only a few cells in the first row, it printed the cell borders but not the information contained within for the rest.
All of the other pages printed fine, any tips? - would be appreciated.

Thanks,
Adam Parker


I have a worksheet that consists of 277 pages. I have a macro that hides pages if they end with a $0 amount. When I print, I want only the visible pages to print, but all 277 pages are printing. The hidden pages actually just come out as blank sheets of paper, but who wants to go through 277 sheets of paper and pull out all of the blank ones?


I need to copy a row of cells from one worksheet to a column of another
worksheet. I want it to reference the cells listed in the row of one
worksheet to the column of the other worksheet. I found out how to do the
reverse - =offset('worksheet name'!$a$1,0,row(1:1)-1) - but it doesn't work
for copying rows to columns? Can you help?



Enigma: there are cells in my workbook that are "blank" even when you hover the mouse over the cell the formula bar is blank. However when I print the page I get 8 blank pages. When I go to print preview the cells are blank but excel thinks that it is used so it is not blank.
when I use formula -=isblank(G9) it returns a FALSE VALUE. But it is actually empty.
I created this formula to delete blank cells in volums G and H but my macro is bombing.

Sub Macro1()
'
' Macro1 Macro
'
Dim wks As Worksheet
For Each wks In Worksheets
Columns("G:H").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
Next wks
End Sub

It works on a few sheets and then ingnore other sheets in the workbook.
Can someone point me in the right direction?
Thanks


Hello,

This is my first post.....

Thanks in advnace for any help you might provide.

In Excel 2007 -- I have created a pricing tool that uses the autofilter function to hide rows (which are full pages when printed) of product line items I dont want to include in the final proposal printout.

In order to get the format to look good when printed, I have to use page breaks throughout the sheet. The sheet includes about 300 rows which I have broken into 10+ pages (using page breaks)

Also note that I have specified the print area that excludes only the columns I want to print. For example, the column that contains the autofiter, is NOT in the print range as I dont want it showing up on the printout. Note that the columns in the print area do fit in the right/left print margins.

The problem is that when I apply the autofilter and hide rows, excel inserts blank pages for those pages that are hidden/filtered out. If I was printing to a printer I could just remove the blank pages and that would be fine. However, I am needing to print this to a PDF so I cant have blank pages.

To clarify further, Lets say I have a 10 page document/worksheet prior to autofiler being applied. After autofiler is applied it cuts its down to 3 pages on the screen which looks great. However, when I print it, excel spits out the 3 pages with text along with 7 blank pages in place of the pages are that are filtered out/hidden.

I have tried removing the hard page breaks and inserting blank rows to get the pages to fall on the natural page breaks but when I apply the filter it messes up my formatting.

I have searched the web for a macro that would print only visable rows on a sheet but have not been able to find one. I did find another post of the same issue, but the suggestion was to remove the page breaks, which does not solve my problem.

Any ideas would be much appreciated.

Thanks
Craig


Hello,
I have a single excel worksheet, and due to area's of the sheet I do not
want to have printed, (the data is to be printed onto pre printed forms),
have used the set print area option to exclude those area's.

The problem is now I have multiple pages inside a single worksheet. Each
page prints seperatly, so I end up with 5 pages with only a small amount of
the full worksheet on each.

Thanks in advance
Peter



I have created a workbook with 19 identically formatted worksheets in it. Each worksheet appears on-screen printing neatly on 3 sheets of paper as desired. This formatting appears consistently in all views while I am working on it and in the print preview. However, when I go to print the entire workbook, only the first worksheet keeps that formatting. Worksheets 2-19 get much larger and no longer print neatly onto 3 sheets of paper each. If I print each one individually, the formatting remains correct and everything is fine. I have checked my page breaks, my scaling percentage, my margins, that everything is centered appropriately, and have reset each one of these things on every worksheet and saved the settings. Likewise, the problem also occurs when printing a single worksheet with multiple pages. If only one copy is printed, the whole thing maintains its formatting. If more than one copy is printed, all of the formatting changes forcing it onto many more pages than appearing on-screen. Others in my office with Excel2010 are experiencing the same problem; nobody has been able to figure out a solution. Those using Excel 2007 can print the same documents with no problems at all. Is there a setting I am missing somewhere that needs to be changed?