Printing Only Rows With Non-blank Cells

Mar 3, 2007

I am trying to print only the non-empty rows in my sheet ("1"), so I am checking every row from row 1 to row 500 to determine if that row is empty, in that case it will be hidden temporarily, then I will PrintPreview only non hidden rows. Finally, I will unhide all rows.

The problem is that although the following code works, it takes forever to run due to large number of rows. Based on sheet design, once the first empty row is discovered, all the following rows beneath it are all blank by default, so all I really need to do is automatically hide all the rows following the first discovered blank one without checking them, then proceed with PrintPreview as above. Sometimes my 3rd row is blank, so I could save a lot of time by automatically hiding rows 3 to 500 without checking them.

code below:

Sub Hide_Print_Unhide()
Dim rw As Long
Application. ScreenUpdating = False

With Sheets("1")
For rw = 1 To 500
If Application.WorksheetFunction. CountA( _
. Cells(rw, 1).Range("A1:C1")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintPreview ' for testing use .PrintPreview
.Range("A1:A500").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

View 7 Replies


ADVERTISEMENT

Stop Blank Rows Printing

Dec 1, 2006

I tried to reduce the size of the printing area by deleting the blank rows. To achieve this I entered VBA code that appeared in the newsletter issue 3 after slightly modifying the code suiting to my need. But, while exeucting the code with all the parameters, xlCellTypeBlanks, xlCellTypeFormulas,xlErrors, the error as shown as a screen shot herein is populated. But, with the parameter,""xlCellTypeBlanks"" all the Blank Rows only got deleted and similarly only those rows containing Formulas are deleted, while the parameter is""xlCellTypeFormulas".

I want all the cells that contain xlFormulas, xlErrors and xlCellTypeBlanks as well be removed while executing the code given herein. But, if the range contains "xlFormulas" and "xlCell TypeBlanks", the rows containing blank cells are not removed and vice versa.

View 2 Replies View Related

How Do I Keep From Printing Blank/Empty Cells

Oct 20, 2008

My work book developes text based on a series of answers. Some areas only apply to certian senarios. How do I keep from printing the 0 value feilds?

View 6 Replies View Related

Count Blank Cells Within A Range Not Including Fully Blank Rows

Jul 15, 2008

I can count the blank cells withiin a range using

=COUNTBLANK(C6:AD2506)

But I dont want it to count the cells if the entire row, within that cell, i.e. C6:AD6, is blank.

It should only count the blank cells within a row if there has been some data entered on that row..provided it has been entered within the specified range.

View 14 Replies View Related

Printing Blank Page

May 31, 2013

I have a set of pages I'd like to print on both sides of the paper, in excel the pages are laid out side by side.
I'd like to print them so that the double page spread is on two bits of paper, as in a book, then, you can turn the page and see the next double page.

At the moment, when I print, I get page one, then turn and see pages 2 & 3.

How do I print a "page 0", then turn over to see pages 1 & 2?

View 1 Replies View Related

Insert Blank Cells Not Rows

Apr 2, 2012

I have the following macro that inserts a blank line when a field changes. This works fine. However I would like to use this in another spreadsheet that has several addition columns of unrelated data. Therefore I want to insert 1 (row of) blank cells only in the columns specified in the range. The range is 3 columns wide, E - G. I think I'm close, but ...

Dim myRow As Long
myRow = 3 'data starting row
Do Until Cells(myRow, 5) = "" ' the # is the column that changes which I want to trigger a blank insertion
If Cells(myRow, 5) = Cells(myRow - 1, 5) Then
myRow = myRow + 1

[Code] ..........

View 6 Replies View Related

Delete Rows Where 2 Cells In Same Row Is Blank

Oct 18, 2006

I want to compare the data in column B and C of Row 2 through X (X being up to 20,000), and if BOTH B and C are blank, delete the entire row.

View 4 Replies View Related

Delete Rows With Blank Cells

Jan 8, 2007

I need a Macro that will delete rows within a specific range that contain blank cells or preferably delete the rows where the first cell in the row contains a blank cell.

View 9 Replies View Related

Delete Rows With Some Blank Cells

Jan 17, 2007

I went to the Macros page and pulled this macro to remove rows if they have blank cells:

Sub DeleteBlankRows2()
'Deletes the entire row within the selection if _
some of the cells WITHIN THE SELECTION contain no data.
On Error Resume Next
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
On Error Goto 0
End Sub

I ran this and nothing happened. Does anyone have a fix or perhaps a better way to make this happen?

View 2 Replies View Related

Printing Code - Print Everything Down The Sheet Until It Sees A Blank In Col A

Apr 27, 2009

I want to have code that will print everything down the sheet until it sees a blank in column A. I have included the code i am trying but it keeps giving me errors.

View 3 Replies View Related

Macro To Delete Rows Where There Are Blank Cells

Sep 25, 2013

I would like code that will delete all rows where there are blank cells in Col D from row2 onwards.

View 2 Replies View Related

Hide Rows Based On Blank Cells?

Feb 21, 2014

I am attempting to hide a series of rows based on if the cells in that row are blank. The catch is that the field of data in the column may vary as follows:

D E F G
x x X x
x X x
x

I would like to eliminate all the rows past the last X value in Column D for example

Below is the code I am attempting to use

Rows("41:60").Select
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 42

[Code].....

View 8 Replies View Related

Filter To Hide Rows If It Contains Blank Cells

Jul 28, 2014

I have a list of about 200 companies in column a. Columns B, C, D, E, etc. list revenues for 2005, 2006, 2007, etc. The problem is not all of the years have values. Is there a way to filter out the companies that have a blank cell for any of the years? For example, if company 1 has a blank in 2007 can I filter it out, even if all of the rest of the revenues are filled in?

View 2 Replies View Related

Page Breaks Result In Blank Pages Printing When AutoFilter Used

Jan 30, 2008

I have a sheet with a few hundred rows of data which I filter out by using a very simple autofilter. I have set page breaks throughout the whole sheet on certain rows I need to start on a new page. If the auto filter filters out any of these rows when it prints it prints a blank page where that row would have been before it was hidden by the autofilter.

For example the whole list is say 5 pages (set with page breaks) I apply an auto filter that now reduces it down to 2 pages say the 1st and last page of the original list. If I page break preview it, it shows me that it will be 2 pages but when i print preview/print the sheet it prints the 1st page then 3 blank pages then page 5 even know that the page break preview only shows and numbers 2 pages. Because the original page breaks are still there just are hidden by the autofilter so it adds them in as blank pages

View 4 Replies View Related

Blank Rows From Merged Cells In Dropdown Menu

Feb 20, 2006

Merge a cell to the cell below it. Now merge the cell below the merged cell to the cell below it. Make one more merged cell. Now put text in each one like this "excel", "hates", "me". Now click on a different cell somewhere else on the page and go to menu Data --> Validation. Choose "List" and then in the source click the little button to the right and then highlight your three cells. Now you have a drop down menu in the somewhere else cell.

But when you go to the dropdown menu it has blank spaces in between the entries (because of the merged rows). If you do the same thing but the list is with cells that are not merged to the rows below then it doesn't have the blank rows. If you are only dealing with cells that are merged in twos it is okay because it is spaced all pretty and everything. The problem is when the cells are not merged evenly so everything looks uneven.

how to get rid of the apparent blank entries in the drop down menu?

View 2 Replies View Related

Insert Multiple Blank Rows At Designated Cells

Jun 13, 2014

I have a large list of cells in excel: 15, 33, 90, 102, 149, 159, 217, 228, 238, 247, 305, 312, 369, 417, 428, 486, 538, 548, 606, 621, 671, 679, 737, 805, 816, 874, 915, 923, 981, 1029,1038 .

Under each of these cells I would like to insert 20 blank rows. I have tried various codes but i'm struggling with the fact that as soon as I insert 20 rows at cell 15, all the other cellnumbers change.

This is a reformulation of this post: [URL] ...........

View 1 Replies View Related

Deleting Rows Where Cells In Multiple Columns Are Blank

Apr 17, 2013

I work with a spreadsheet every week to input values and subtotal them. These values change constantly and instead of going through and manually deleting each row in a 100+ row spreadsheet to be able to import into another program, I'm looking for an quicker way to keep my data in order but consolidate by getting rid of only the rows where both column A and B are blank.

Here's an example of what I'm working with:

5
10
15
20
50
2
4
6
8
20

This is what I need the final product to look like:

5
10
15
20
50
2
4
6
8
20

So I would like to quickly delete rows 2, 5, and 11. All of the other answers I've found only show how to delete rows based on empty cells in only 1 column. How can I quickly delete the rows where both columns are empty?

View 5 Replies View Related

Combine/merge Row Cells Text Between Blank Rows

Jun 14, 2009

I have a large list of text in one column which i need to combine between blank rows into one cell or a new column, the number of rows to be combined varies.

for example

aa
bb

ww
xx
zz

rr

gg
hh
ii
jj

would become

aa bb
ww xx zz
rr
gg hh ii jj

I have over 30000 lines so doing it by hand is not an option.

View 9 Replies View Related

Delete Rows With Blank Cells In A Specific Range

Feb 23, 2010

I need a VBA to delete rows with blank cells within columns F - AZ
Columns A - E contain headers but also need to be deleted if cells in columns F -AZ are blank.

View 9 Replies View Related

Delete Rows With Blank Cells But Housing Formulas

Sep 1, 2009

I have several lists that I need to be able to print out periodically.

The main worksheet that underpins each list is over 900 lines long and numerous calcs and formulae populate columns to provide the data for my 'print lists'.

I need my 'print lists' on seperate worksheets and I have linked the data from my main worksheet results, using IF statements to stop unwanted results being displayed by making the rows appear empty (i.e. to display "").

Due to the design of my main Worksheet it is inevitable that I have many rows in each 'print list' that are not required for that particular 'print list'.

I want to remove the 'empty' rows from the print sheets at the time of printing. I cannot use the GoTo > Special > Blanks > Delete Rows because the rows are not recognised as being 'blank'.

I have attached 2 screenshots of my 'print lists'.

P.S. I am not assuming anything but I have searched on OZGRID and it appears to me that I might need to use Excel/VBA?

View 9 Replies View Related

Delete Rows Based On Adjacent Blank Cells

Aug 8, 2008

I would like a macro that deletes rows based on having two blank cells in adjacent columns. I have achieved this with the following code however i need it to only delete rows below a certain row. How would i achieve this?

Sub DeleteBlankARows()
Dim r As Long
For r = Cells(Rows.Count, 30).End(xlUp).Row To 1 Step -1
If Cells(r, 3) = "" And Cells(r, 4) = "" Then Rows(r).Delete
Next r
End Sub

View 2 Replies View Related

Convert Multiple Columns To Rows Including Blank Cells

Apr 11, 2014

I currently have a spreadsheet that I had to convert from multiple rows to columns:

[URL]

Now I need a script to change the data so that each column is now in row format, (see attached spreadsheet).

View 5 Replies View Related

Transpose Duplicate Rows Into Columns Including Blank Cells

Mar 6, 2014

I found this great macro to use in a spreadsheet I'm trying to transpose from rows to columns, however, I need a place holder for blank cells: [URL] .....

I was able to use the macro in the last post by Ochenden but the blank cells need to have a placeholder.

How to change the macro or come up with a different script for me to use?

Attached is how I need the spreadsheet to look.

Script I used:

Sub aaa()
Dim OutSH As Worksheet
Set OutSH = Sheets("Sheet2")

Range("A:A").AdvancedFilter Action:=xlFilterCopy, copytorange:=OutSH.Range("A1"), Unique:=xlYes

[Code] ..........

Transpose_Duplicates_Examples.xlsx

View 2 Replies View Related

Macro To Copy Rows Containing Specific Blank Cells To Another Workbook

Jan 30, 2013

I have spreadsheet of data, I need to extract any rows that have blanks cells in columns F or P or T.

If possible I would like a macro I could run that would cut all of the rows that meet the above criteria and paste them in to a separate sheet.

View 2 Replies View Related

Excel 2007 :: Inserting Number Of Blank Rows Between Cells

Mar 5, 2013

I have a worksheet with four data columns A,B,C,D starting in row 2. I want to add between every row, exactly 11 blank rows. So if we have:

Code:

A B C D

1 5 3 4
2 7 6 3
3 9 1 3

the end result should be:

Code:

A B C D

1 5 3 4
2 7 6 3
3 9 1 3

Do I necessarily need a macro for this? Or is there another quick and creative way to achieve this result?

View 3 Replies View Related

Printing Grouped Rows

Aug 11, 2013

I have a few pages of information and have grouped rows collapsed to show only the summary information. When I try to print only visible summary rows, whether I use print, print selection, copy and paste to another sheet or copy and "paste special" values to another sheet, all the hidden/grouped rows print or the summary rows print separately on different pages. Is it possible to print only the summary rows.

View 3 Replies View Related

Printing Only Unhidden Rows?

Jul 23, 2014

I have a worksheet that has rows of information up to row 3000. Sometimes all the rows are shown and sometimes a lot of them are hidden. When I try and print, the rows with info are printed but then the hidden rows are included as just blank pages. Is there a way I can get excel to print only the rows in 1-3000 that are shown and not hidden?

View 1 Replies View Related

Hide Rows Before Printing

Sep 6, 2007

I've a worksheet that contains a whole list of items in stock.

For example
Item Quantity
hot water bottle
maggi
fab
cooking oils
breakfast oats

The above is an example of what might be seen in the spreadsheet. What i'd like to be able to do is before printing it out, i want items with 0 quantity to be shown only. So i decided to hide the rows that have items with no quantity. To do this, all i could think of is to have a button that may contain codes to hide the rows. The problem with that is the button will appear there in the printout. Is there any way of making the rows hidden before printing without using a button to trigger the code?

View 14 Replies View Related

Unhide Rows When Printing

Nov 28, 2007

Is there a VB routine that can unhide rows when I print?

View 9 Replies View Related

Removing Rows For Printing

Mar 10, 2006

I have a report that pulls data from another worksheet in Range A26:J58.
What I want to be able to do is assign a button for printing the report but
before printing from A1:J70 removing or hiding any row in the A26:J58 range
where there is no data. The range is filled from row 26 down so it is not a
random fill.

Also, I want to save this workbook as a template so that it can be used over
again, so I guess it would not be good to delete the rows in the range
otherwise I would have to recreate them.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved