Free Microsoft Excel 2013 Quick Reference

Format worksheet to view only the print area cells in Excel

Simple I know but have stumped everyone. How do I format a worksheet to view
only the selected print area in the working area. In other words I do not
want to see any other columns or row numbers - just a gray background which
makes the worksheet look like a clean piece of paper or form when you open it


Post your answer or comment

comments powered by Disqus
to replace only the last text/chracter in a cell

How do I suppress printing of cells in Excel that have a conditional equation
in the cell and appear as blanks in the worksheet, but when I print I get a
blank label. I am setting this up for a user and I don't want them to have
to highlight the cells and then use SELECTION in the print parameters?

How do you create a trendline for only the x-axis values in excel?

I have a worksheet with lots of complex formulas that my coworkers will all be using. I have the print area set to print just one page. I'd like to hide the non-printed pages when viewing the sheet in page layout view. Any ideas?
Thanks

Hi

I want to limit - via VBA - the amount of rows I print to only those rows that contain printable data, so that I dont have to manually adjust the print area. The amount of columns is fixed.

can anyone help?

Any ideas how I can email only the print area of a worksheet? I also need to
be able to save only the print area into another file. Both of these have to
be done with absolutely no technical expertise, in other words just push a
button, no copy and paste or anything else so technical!

Hello. Is there a way to total only the print area you've selected. IE.
Though you may have a running total of the whole sheet, I just want the total
of the area I selected to print. I would like a way that doesn't require me
to have to change the formula each time i select certain lines to print.
Thanks

I would like Excel to automatically adjust the print area for a pivot table.
As I select different options, the # of rows change and I have to manually
select the print area to receive the correct printout.

I found one other post about this and I tried to follow the instructions but
I couldn't get it to work. I think I had to write a macro but I'm not sure.
This was what the other thread suggested:

dim i as long
dim j as long
dim rng as range
set rng=range("b1..e1") ' the address of first row of data you want to
print out
j=0
for i = 1 to rng.columns.count
j=worksheetfunction.max(j,rng.cells(application.Rows.Count-rng.Row,1).end(xlup).row)
next i
activesheet.pagesetup.printarea =
rng.Resize(j-rng.row+1,rng.Columns.Count).Address

I consider myself to be an advanced user in Excel but I'm not good with
writing code. I can record a new macro, no problem --> but I'm a novice at
writing one.

If you have a solution to this problem, please write the instructions for a
"dummy". I won't take it personally

I am using Excel 2003.

--
Thanks!

Hi,
I have the following code for creating a new worksheet [CREDIT: JINDON]


	VB:
	
 
Function IsSheetExists(ByVal sn As String) As Boolean 
    On Error Resume Next 
    IsSheetExists = Len(Sheets(sn).Name) 
    On Error Goto 0 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
How can I modify it so it creates worksheets with worksheet titles based on the values of cells in a certain range?

E.g. if I specify A1:A3 and the values in A1 is dog, A2 is cat and A3 is bird, 3 new sheets will be created with sheet titles dag, cat and bird repectively?

Thanks thanks ((:

How do you clear the print quality setting in Excel after its been changed to
a specific resolution?

I have a workbook with 25 sheets - 20 sheets are similar and require the PRINT AREA to be set the same in each sheet. I cannot group them as the Option to SET PRINT area is then not available in 2007. I have used this code but it sets the print area on all the work sheets in the workbook. I have tried to alter the code myself to only include those sheets that I have selected but have had no success.


	VB:
	
 
Sub PRINTAREASETALL() 
    Dim strPA As String, Sht As Worksheet 
    On Error Goto NOT_RANGE 
    strPA = Selection.Address 
    For Each Sht In ActiveWorkbook.Worksheets 
        Sht.PageSetup.PrintArea = strPA 
    Next 
    Exit Sub 
    NOT_RANGE:  MsgBox "Select the Print Area Cells, then try again!" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be appreciated.

Dear Sir,
I am not that expert in Excel, My problem is, we are using the software in which there is an option of Export to excel, when i use that the contents of my document comes to excel but every time i have to set the page Break, so what i want you to help is I will set a print area in book1 and save it as templete so when every i am exporting any file to XL, the print area will be taken from that Templete.

Note : when i tranfer the file Evey time its only one page.
Please explain to me in detail as i am not that expert in excel.

I have a spreadsheet that I would like to have the user select their items
then they can click on a macro button to have it bring all of the selected
items to the top of the page and then the macro will automatically reset the
print area so that only the selected items will print. How do you do that

I have a macro that allows a user to select from a listbox (on a UserForm) the name of an existing (hidden) worksheet. The macro then creates a blank worksheet and copies the contents of the original onto it.
I have managed to copy the cell contents, PageSetup.Zoom, etc. but have not found out how to copy over the Print Area (that is I want to set the Print Area on the new sheet to be the same as on the first). Is there a straightforward way of doing this?

Thanks

I have been searching without real success for a soloution to a print area problem. Hopefully this one us easy for those blessed with the more ability than me.
I have a worksheet named "TQUOTE" with the print area defined as A1:E:286 resulting in 5 pages printing ,however if there are only 3 pages of data I will be left with 2 blank pages being printed. ( along with the header rows which are set to repeat for each page ). I should mention this worksheet is included in a list of sheets to print based on the response to a user form.
Would it be possible to have the print area change based on the an empty cell that related to the next page break? eg. If the page break for page 1 ends at row 55, if a cell in row 56 were empty then set the print area to be A1:E55
If the page break for page 2 ends at row 85, if a cell in row 86 were empty then set the print area to be A1:E85 and so on ??
I do not have the skills yet to write this macro , and i bow to thiose that do !
any and all help would be greatly appreciated.
Jeff

I have a worksheet that has a formula =IF($B13>0, $A12+1, "") in cell A13. The formula is copied down in column A. Cell A12 has a value of 1. What this will do is to sequentially number each successive row in column A when the value of the corresponding cell in column B is greater than 0.

This works great, except for when I want to print. Since I have this formula copied down column A all the way to row 717, the print area is set to print the selection containing all of the formulas, even if the cells are blank.

I tried to enter the formula into the conditional formatting "Formula Is", but it did not put a numerical value into the cell.

I'm very new to VBA, but I think that what I need is a VBA code that will automatically enter a sequential number into column A if the value in column B is not blank (either a number, text, or both), otherwise to leave column A blank.

Lastly, is there a way to make this macro run automatically so that I don't have to click on a button?

Hi,

I am running into a problem specifying a mail merge range in Excel 2002/2003 that only includes non-blank cells. In Excel/Word 2000 I was able to achieve this by hiding unused rows in Excel before performing the mail merge. This also added simplicity to the Excel “form” by showing the user only rows needing to be filled out at the time. In Excel/Word 2002 and beyond, this no longer seems to work, however, one peculiarity seems to make me thing perhaps Excel 2002 and beyond is still capable of limiting a mail merge data source range in this way.

Any ideas of how I might either modify the Excel or Word documents or the VBA code so that only shown/filled rows are included in the mail merge, thus eliminating unnecessary blank pages?

The actual mail merge is being performed using Microsoft Word VBA code (as I ran into far too many problems in executing the mail merge in Excel) while the Excel VBA code is used for showing/hiding cells, clearing the form, determining the number of printed pages in the final step, and saving and continuing the next step in the mail merge process.

Full details to follow and I’d be more than happy to upload screen captures if that would be of help. If you believe this to be a Word VBA solution I will try and find a Word coding forum in which to ask this question.

Thanks much!

-------------------------------------------------------------------------------------

A year ago I came up with an automation “mini-app” that staff can use to generate the correct number of student aide tags needed for the student aides helping them in their classes. This automation uses a batch script, Excel VBA and Word VBA to complete a mail merge in Microsoft Word.

The batch file copies the Excel and Word documents to the user’s Desktop, launches Excel first, inviting staff to fill out the required columns “TEACHER’S LAST NAME,” “STUDENT’S FULL NAME,” “PERIOD # (INCLUDING SUFFIX),” and “TYPE OF TAG.” There is a textbox in column F that allows users to show or hide cells (eight cells are shown by default), a button to clear the form, a button to find out how many printed pages they should expect (based on a mathematical formula that divides the number of shown cells by eight and another formula that divides the number of filled cells by eight) and a button that saves and closes the Excel document.

Upon clicking the “Save, Close and Continue” button in the Excel spreadsheet, the batch script launches the Microsoft Word document on the Desktop where Word VBA code automatically completes the mail merge with the Excel document on the user’s Desktop and initiates the printing of the completed merged document to the default printer, closes the Word mail merge template document and (upon closure of the Word application) deletes the temporary documents off the user’s Desktop.

The version of Microsoft Word used in our building prior to the upcoming school year was Microsoft Office 2000 (the OS is Windows XP). However, we recently upgraded all of our licenses to Microsoft Office 2003 and I soon realized that I also needed to make a few adjustments to my “mini-app” in order for it to work with the upgraded version of Office.

Everything is now working as it should except for one interesting little bug. In column F of the Excel spreadsheet I added a textbox with some VBA code to hide/show rows in the spreadsheet. By default, only nine rows (eight plus the header row in row 1) show as there are eight student aide tags to a printed page once the mail merge has been completed in Word. The instructors can use the textbox to hide or show additional rows as they need to. This works to simplify the user’s view by allowing them to only show the number of rows needed but also, in Excel/Word 2000, creates a situation where (by default) Word only performs a mail merge against the unhidden cells. This limits the number of pages printed and eliminates the unnecessary printing of blank pages following the completed mail merge page(s).

The rub is this. In Excel 2000 hiding unused rows eliminates the blank pages from being generated in Word by limiting the data source range. However, in Excel 2002 and beyond, hiding rows does not limit the data source range in Word and a number of blank pages (based upon the number of rows unhidden in the first place) are generated following the final mail merge. It doesn’t seem to make a difference if I manually rehide the rows or rehide these using the VBA code associated with the textbox.

For example, the Excel document starts with only nine rows unhidden, and if I precede without unhiding any additional rows only one printed page is generated in Microsoft Word. However, if I unhide say the first 50 rows and then, prior to saving and continuing, decide to rehide all but the first nine rows, six additional blank pages are still generated in Microsoft Word upon the completion of the mail merge. Blank pages occur when unhiding more rows than the user fills out.

What I find interesting about this is that there has to be something saved in the Excel 2003 document upon hiding/unhiding rows. I can temporarily rectify the additional blank pages problem by copying the original, untouched, Excel document and reapplying the mail merge. So I’d deduce that there must be something saved in the original, untouched, Excel 2003 document that allowed Word to limit the data source range to only the non-hidden cells; There must be something that changes in the Excel document when I unhide/rehide cells? However, I have not yet been able to duplicate the results.

Any ideas of how I might either modify the Excel or Word documents or the VBA code so that only shown/filled rows are included in the mail merge, thus eliminating unnecessary blank pages?

Thanks much!

-Matt-

----------------------------------------------

Here is the VBA code from the Microsoft Excel file:

Excel - Sheet 1 (Generate Aide Tags App):

Option Explicit
Private varNumCellsToShow
Public varReturnDetected

Private Sub btnHowManyPrintedPgs_Click()
Call HowManyPrintedPgs
End Sub

Private Sub btnNumStudsEnter_Click()
On Error Resume Next
Application.ScreenUpdating = False
varNumCellsToShow = txtNumCellsToShow.Text + 2
If txtNumCellsToShow.Text = 65535 Then
MsgBox ("You entered a number greater than or equal to 65535. Showing all 65535 rows.")
txtNumCellsToShow.Text = 65535
varNumCellsToShow = 65536
Range("B1").Select
End If
If varNumCellsToShow = "" Then
Exit Sub
End If
Cells.Select
Selection.EntireRow.Hidden = False
Rows(varNumCellsToShow & ":65536").Select
Selection.EntireRow.Hidden = True
varNumCellsToShow = ""
End Sub

Private Sub btnSaveAndClose_Click()
ActiveWorkbook.Save
Excel.Application.Quit
End Sub

Private Sub bttnClear_Click()
Application.ScreenUpdating = False
ActiveWindow.SmallScroll Down:=-33
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("B2").Select
End Sub

Private Sub txtNumCellsToShow_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
varReturnDetected = 1
Call btnNumStudsEnter_Click
End If
End Sub

Private Sub txtNumCellsToShow_LostFocus()
If varReturnDetected 1 Then
Call btnNumStudsEnter_Click
End If
varReturnDetected = 0
End Sub

Excel - Module 1:

Option Explicit
Private mycount As Long

Sub HowManyPrintedPgs()
Dim FinalRow
Dim LastFilledRow
Dim NumberofPages As Integer
Call NumberUnhiddenBlank
If mycount = 0 Then
FinalRow = (Range("B65536").End(xlUp).Row)
If FinalRow 7 Then
NumberofPages = WorksheetFunction.RoundUp((FinalRow - 1) / 8, 0)
MsgBox ("There are 8 student aide tags to a page and you have elected to print " & FinalRow - 1 & " tags. Tags will print to " & NumberofPages & " pages.")
End If
End If
If mycount > 0 Then
FinalRow = (Range("B65536").End(xlUp).Row) + mycount
LastFilledRow = (Range("B65536").End(xlUp).Row)
If FinalRow 2 Then
NumberofPages = WorksheetFunction.RoundUp((FinalRow - 1) / 8, 0)
MsgBox ("There are 8 tags to a page, you have elected to print " & LastFilledRow - 1 & " tags. IMPORTANT NOTE: You have " & mycount & " blank rows at the bottom of your form. To reduce the number of pages printed, please be sure to adjust your Number of Tags to Create textbox settings. Tags will print to " & NumberofPages & " pages.")
End If
End If
End Sub

Sub NumberUnhiddenBlank()
Dim c As Range
mycount = 0
For Each c In Range("B1:B65536")
If Rows(c.Row).Hidden = False And c.Value = "" Then mycount = mycount + 1
Next c
'MsgBox ("The number of unhidden, blank, rows is " & mycount & ".")
End Sub

Here is the VBA code from the Microsoft Word file:

Option Explicit

Private Sub ActivateMailMerge_Click()
Call Document_Open
End Sub

Private Sub Document_Open()
Dim UserName
UserName = Environ("username")
MsgBox ("This step will take a couple of seconds. Please hang on...")
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:Documents and Settings" & UserName & "DesktopStudent Aides.xls", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet", SQLStatement _
:="", SQLStatement1:=""
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With

Dim NumPgs As Long

NumPgs = Selection.Information(wdNumberOfPagesInDocument)

If NumPgs < 2 Then
Select Case MsgBox("Please insert " & NumPgs & " page of purple colored paper into the printer and press OK to print or press Cancel to cancel", vbOKCancel, "Printing to the Default Printer...")
Case vbOK
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0
Case vbCancel
Documents(2).Close SaveChanges:=False
Exit Sub
End Select
End If

If NumPgs >= 2 Then
Select Case MsgBox("Please insert " & NumPgs & " pages of purple colored paper into the printer and press OK to print or press Cancel to cancel", vbOKCancel, "Printing to the Default Printer...")
Case vbOK
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0
Case vbCancel
Documents(2).Close SaveChanges:=False
Exit Sub
End Select
End If

End Sub

I'm looping through a number of reports, and the print area is going to vary, depending on how many columns of data I have present. The starting cell will always be A1, but sometimes the print area will end at D1, E1, F1, etc.

Is there a way to use VBA to set the print area for each report, by refering to a worksheet containing the correct # of columns of data present.

In worksheet "Count," cell A1 = 3. This would mean the print area of my report would be A1 to (A1+3).

Hi,

I looked, honest.
The HELP was no help, either.

Gosh, things were sure simpler with the earlier versions.

Anyway, how do I go about setting the Print-Area boundaries; the area I
would like to print (only) ?

Thanks,
Bob

I've used the following method from D. Peterson to automatically set
the print area to extend to the last row which contains actual data.

Now, I'm faced with a similar situation which the 'columns' are now
the variable component.
Rows to be printed begin at A1:A18 and will always include columns A
and B; there may be as many as 40 columns to include, or as few as the
1st two

These are the 2 components of the named ranges which will set the
number of rows(for reference):>>

Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000""),ROW(Sheet1!$A$1:$A
$1000))

(Make that 1000 big enough to extend past the last possible row.)

Then once mo
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3)

That last 3 represents the last column to print (A:C in my example).

And change the worksheet (sheet1) if necessary (in all the places).>>

Any ideas? I'd like to keep the named range concept if possible
Thanks for any thoughts.

Pierre

How to get only the year in the date format
I.e in the table in need to display only year
E.g 2005 - should be display " 05" automatically

Hello,

Ok so I need to set the print area with a variable number of cells. I've tried several different methods but nothing works. What I've tried so far is:

Worksheets(2).Activate
Worksheets(2).PageSetup.PrintArea = "$A$1:Cells(q + 4, 9)"

also tried:

Worksheets(2).Activate
Worksheets(2).PageSetup.PrintArea = Range("A1", Cells(q + 4, 9))
No luck with either of those. Any ideas? Thank you!!

I have a workbook with four pages, each with the same number of columns, but
various numbers of rows. I have a macro to automatically set the print area
for those pages and it works just fine. Now I have to add a column to just
one of the pages in the workbook. The macro I have naturally excludes the
new last column. I suspect more changes are on the horizon i.e some pages
growing, others shrinking. I'd rather have Excel 'see' what columns are
present, rather than continually altering the code when I make changes. I
tried to use XLRight instead of naming the column "U", but that doesn't work.
I think I'm on the right track but probably not using the correct syntax.
Here is the code. Any one have any ideas?

Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
Dim LastRow As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
.PageSetup.PrintArea = "A1:U" & LastRow
End With
Next

Hi everyone,

Is it possible to sum only those values that fall within the print area? I have a spreadsheet of overtime hours by unit and by each payroll period. It looks something like this:

Unit 1 10 hours
Unit 2 15 hours
Unit 3 20 hours

And it's repeated below like that for all 26 payroll periods. On top I have a running total for the year, and use set print area to show only those payroll periods that I want. Instead of updating the sum formula everytime I adjust the print area, I was wondering if I can have something that will add the values after I reset the print area.

Thanks so much.


No luck finding an answer? You could always try Google.