Cách In 1 Vùng Chọn Trong Microsoft Excel

In this tutorial, you will learn how khổng lồ select print area in Excel manually & how lớn set print ranges for multiple sheets by using macros.

Bạn đang xem: Cách in 1 vùng chọn trong microsoft excel

When you hit the Print button in Excel, the entire spreadsheet is printed by default, which often takes multiple pages. But what if you don"t really need all the content of a huge worksheet on paper? Luckily, Excel provides the ability define the parts for printing. This feature is known as Print Area.

Excel print area

A print area is a range of cells to lớn be included in the final printout. In case you don"t want to print the entire spreadsheet, phối a print area that includes only your selection.

When you press Ctrl + phường or click the Print button on a sheet that has a defined print area, only that area will be printed.

You can select multiple print areas in a single worksheet, và each area will print on a separate page. Saving the workbook also saves the print area. If you change your mind at a later point, you can clear the print area or change it.

Defining a print area gives you more control over what each printed page looks like and, ideally, you should always set a print area before sending a worksheet khổng lồ the printer. Without it, you may end up with messy, hard khổng lồ read pages where some important rows & columns are cut off, especially if your worksheet is bigger than the paper you are using.

How khổng lồ set the print area in Excel

To instruct Excel which section of your data should appear in a printed copy, proceed in one of the following ways.

Fastest way lớn set print area in Excel

The quickest way to set a constant print range is this:

Select the part of the worksheet that you want to print.On the Page Layout tab, in the Page Setup group, click Print Area > Set Print Area.

*

A faint gray line will appear denoting the print area.

*

More informative way khổng lồ define print area in Excel

Want to visually see all your settings? Here"s a more transparent approach khổng lồ defining a print area:

On the Page Layout tab, in the Page Setup group, click the dialog launcher
*
. This will xuất hiện the Page Setup dialog box.On the Sheet tab, put the cursor in the Print area field, & select one or more ranges in your worksheet. Lớn select multiple ranges, please remember to hold the Ctrl key.Click OK.

*


Tips & notes:
When you save the workbook, the print area is also saved. Whenever you send the worksheet lớn the printer, only that area will be printed.To make sure the defined areas are the ones you really want, press Ctrl + p. And go through each page preview.

How to lớn set multiple print areas in Excel

To print a few different parts of a worksheet, you can select multiple print areas in this way:

Select the first range, hold down the Ctrl key & select other ranges.On the Page Layout tab, in the Page Setup group, click Print Area > Set Print Area.

Done! Multiple print areas are created, each one representing its own page.


Note. This only works for non-contiguous ranges. Adjacent ranges, even selected separately, will be included in a single print area.

How to lớn force Excel to lớn ignore print area

When you want a hard copy of a whole sheet or entire workbook but vì chưng not want lớn bother clearing all the print areas, just tell Excel khổng lồ ignore them:

Click File > Print or press Ctrl + P.Under Settings, click the arrow next to Print Active Sheets and select Ignore Print Area.

*

How lớn print multiple areas on one page

The ability khổng lồ print multiple areas per sheet of paper is controlled by a printer model, not by Excel. To kiểm tra if this option is available to lớn you, press Ctrl + P, click the Printer Properties link, and then switch through the available tabs of the Printer Properties dialog box searching for the Pages per Sheet option.

*

If your printer has such an option, lucky you are :) If there is no such option, then the only way I can think of is copying the print ranges to lớn a new sheet. With the help of the Paste Special feature, you can links the copied ranges lớn the original data in this way:

Select the first print area and press Ctrl + C khổng lồ copy it.On a new sheet, right click any blank cell & choose Paste Special > Linked Picture.Repeat steps 1 và 2 for other print areas.In the new sheet, press Ctrl + p to print all the copied print areas on one page.

*

How lớn set print area in Excel for multiple sheets with VBA

In case you have a lot of worksheets with exactly the same structure, you will obviously want to output the same rage on paper. The problem is that selecting several sheets disables the Print Area button on the ribbon. Luckily, there is an easy workaround described in How khổng lồ print the same range in multiple sheets.

If you have to print the same area on multiple sheets regularly, the use of VBA can tốc độ up things.

Set print area in selected sheets as on the active sheet

This macro automatically sets the print area(s) for all selected worksheets the same as on the active sheet. When multiple sheets are selected, the active sheet is the one which is visible when you run the macro.


Sub SetPrintAreaSelectedSheets()Dim CurrentPrintArea As StringDim Sheet As WorksheetCurrentPrintArea = ActiveSheet.PageSetup.PrintAreaFor Each Sheet In ActiveWindow.SelectedSheetsSheet.PageSetup.PrintArea = CurrentPrintAreaNextEnd Sub
Set print range in all worksheets as on the active sheetNo matter how many sheets you have, this code defines the print range in a whole workbook in one go. Simply, set the desired print area(s) on the active sheet và run the macro:


Sub SetPrintAreaAllSheets()Dim CurrentPrintArea As StringDim Sheet As WorksheetCurrentPrintArea = ActiveSheet.PageSetup.PrintAreaFor Each Sheet In ActiveWorkbook.SheetsIf Sheet.Name ActiveSheet.Name ThenSheet.PageSetup.PrintArea = CurrentPrintAreaEnd IfNextEnd Sub
Set the specified print area in multiple sheetsWhen working with different workbooks, you may find it convenient if the macro prompts you lớn select a range.

Here"s how it works: you select all the target worksheets, run the macro, select one or more ranges when prompted (to select multiple ranges, hold the Ctrl key), và click OK.

*


Sub SetPrintAreaMultipleSheets()Dim SelectedPrintAreaRange As RangeDim SelectedPrintAreaRangeAddress As StringDim Sheet As WorksheetOn Error Resume NextSet SelectedPrintAreaRange = Application.InputBox("Please select the print area range", "Set Print Area in Multiple Sheets", Type:=8)If Not SelectedPrintAreaRange Is Nothing ThenSelectedPrintAreaRangeAddress = SelectedPrintAreaRange.Address(True, True, xlA1, False)For Each Sheet In ActiveWindow.SelectedSheetsSheet.PageSetup.PrintArea = SelectedPrintAreaRangeAddressNextEnd IfSet SelectedPrintAreaRange = NothingEnd Sub

How khổng lồ use the macros

The easiest way is to tải về our sample workbook with Print Area Macros và run a macro directly from that workbook. Here"s how:

Open the downloaded workbook and enable the macros if prompted.Open your own workbook.In your workbook, press alternative text + F8, select the macro of interest, và click Run.

The sample workbook contains the following macros:

SetPrintAreaSelectedSheets - sets the print area in the selected sheets as on the active sheet.SetPrintAreaAllSheets – sets the print area in all sheets of the current workbook as on the active sheet.

Xem thêm: Bài 23: Phong Trào Nông Dân Tây Sơn Mùa Xuân Năm 1771, Tiểu Sử Phong Trào Nông Dân Tây Sơn

SetPrintAreaMultipleSheets - sets the specified print area in all the selected worksheets.

Alternatively, you can save your tệp tin as a macro-enabled workbook (.xlsm) and địa chỉ a macro to it. For the detailed step-by-step instructions, please see How to insert and run VBA code in Excel.

How to change print area in Excel

Accidentally included irrelevant data or missed selecting a few important cells? No problem, there are 3 easy ways to lớn edit print area in Excel.

How to expand print area in Excel

To add more cells lớn the existing print area, just vì chưng the following:

Select the cells that you"d lượt thích to add.On the Page Layout tab, in the Page Setup group, click Print Area > Add to lớn Print Area.

Done!

*

This is of course the fastest way to lớn modify print area, but not transparent. To get it right, here are a few important things khổng lồ remember:

The Add lớn Print Area option appears only when the worksheet already has at least one print area.If the cells you are adding are not adjacent khổng lồ the existing print area, a new print area is created, & it will print as a different page.If the new cells are adjacent khổng lồ the existing print area, they will be included in the same area & printed on the same page.

Edit print area in Excel by using Name Manager

Every time you mix a print area in Excel, a defined range named Print_Area is created, và there is nothing that would prevent you from modifying that range directly. Here"s how:

On the Formulas tab, in the Defined Names group, click Name Manager or press Ctrl + F3 shortcut.In the Name Manager dialog box, select the range you want lớn change & click the Edit button.

*

Change print area via Page setup dialog box

Another quick way khổng lồ adjust print area in Excel is to lớn use the Page Setup dialog box. The best thing about this method is that it lets you make any changes you want – modify the print area, delete or địa chỉ a new one.

On the Page Layout tab, in the Page Setup group, click the dialog launcher (a small arrow in the lower-right corner).On the Sheet tab of the Page Setup dialog box, you will see the Print area box & can bởi your edits right there:To modify the existing print area, delete và type the correct references manually.To replace the existing area, put the cursor in the Print area box & select a new range on the sheet. This will remove all the existing print areas so only the selected one is set.To add a new area, press và hold the Ctrl key while selecting a new range. This will mix a new print area in addition to lớn the existing one(s).

*

How to clear print area in Excel

Clearing the print area is as easy as setting it :)

Open the worksheet of interest.Switch khổng lồ the Page Layout tab > Page Setup group và click the Clear Print Area button.

*


How khổng lồ lock print area in Excel

If you frequently giới thiệu your workbooks with other people, you may want to lớn protect the print area so that no one could mess up your printouts. Regrettably, there is no direct way to lớn lock the print area in Excel even by protecting a worksheet or workbook.

The only working solution khổng lồ protect print area in Excel is with VBA. For this, you showroom the Workbook_BeforePrint sự kiện handler that silently forces the specified print area just before printing.

A simpler way would be to set the sự kiện handler for the active sheet, but this works with the following caveats:

All your worksheets should have the same print rage(s).You will need khổng lồ select all the target sheet tabs before printing.

Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.PrintArea = "A1:D10"End Sub
If different sheets have different structure, then specify the print area for each sheet individually.


Private Sub Workbook_BeforePrint(Cancel As Boolean) Worksheets("Sheet1").PageSetup.PrintArea = "A1:D10" Worksheets("Sheet2").PageSetup.PrintArea = "A1:F10"End Sub
The above macro sets the print area to A1:D10 for Sheet1 và to A1:F10 for Sheet2. You are free to change these as desired as well as to showroom more sheets.

To địa chỉ cửa hàng the sự kiện handler in your workbook, carry out these steps:

Press alt + F11 to open the Visual Basic Editor.In the Project Explorer window on the left, expand the target workbook"s node and double-click ThisWorkbook.In ThisWorkbook Code window, paste the code.

*


Note. For this approach lớn work, the file needs khổng lồ be saved as a macro-enabled workbook (.xlsm) và the macro should be enabled on opening the workbook.

Excel print area problems

Most printing problems in Excel usually relate khổng lồ the printer settings rather than print area. Nevertheless, the following troubleshooting tips may come in helpful when Excel is not printing the correct data.

Cannot set print area in Excel

Problem: You can"t get Excel to accept the print area that you define. The Print Area field shows some odd ranges, but not the ones you have entered.

Solution: Try to clear print area completely, and then select it anew.

Not all columns are printed

Problem: You have selected a certain number of columns for the print area, but not all of them are printed.

Solution: Most likely, the column width exceeds the paper size. Try making the margins narrower or adjust scaling – choose Fit All Columns on One Page.

The print area prints on several pages

Problem: You want the one-page printout, but it prints on several pages.

Solution: Non-adjacent rages are printed on individual pages by design. If you selected just one range but it gets split to lớn several pages, then most probably it is bigger than the paper size. Lớn fix this, try setting all margins close to 0 or choose Fit Sheet on One Page. For more details, please see How lớn print Excel spreadsheet on one page.

That"s how you set, change & clear print area in Excel. I thank you for reading and hope lớn see you on our blog next week!

You may also be interested in


*
awm119 says:

I have multiple sheets with multiple ranges on each that I am exporting khổng lồ a single pdf. My VBA code is working except I can"t find a way lớn set the print titles on multiple ranges that appear on the same sheet. For example the PDFControls below include a row "Assets Inventory" which contains two ranges khổng lồ print ("Assets_Crystal_And_Keepsakes, Assets_Electronics"). I"m unable lớn see how I can change the Titles for each range using my code.

Any thoughts or ideas would be gratefully welcome.

Here is a subset of the code and the range containing my print controls:

For Each R In Range("PDF_Controls").rows SheetName = R.Cells(, 1).Value SheetPrintRange = R.Cells(, 2).Value Sheets(SheetName).PageSetup.PrintArea = SheetPrintRange SheetPrintTitle = R.Cells(, 3).Value Sheets(SheetName).PageSetup.PrintTitleRows = SheetPrintTitle ReDim Preserve pdfSheets(i) pdfSheets(i) = R.Cells(, 1).Value i = i + 1 Next Worksheets(pdfSheets).Select

With ActiveSheet.PageSetup .LeftHeader = "&""-,Bold""&K0070C0" & Chr(10) & "Personal Information" .CenterHeader = _ "&""-,Bold""&14&K08-024Estate Criteria for Executor" và Chr(10) & "Vital Information and Inventories" .RightHeader = "&P" .FitToPagesWide = 1 .FitToPagesTall = False

kết thúc With

RangeName "PDF_Controls" =

Sheet Name Print Range Print Title Rows

Personal Information PI_Personal_Information, PI_Military_InformationFinancial Information Banking_Accounts, Home_Mortgage, Credit_CardsAssets Inventory Assets_Crystal_And_Keepsakes, Assets_Electronics CrystalTitles

Leave a Reply

Your email address will not be published. Required fields are marked *

  • Cách xin nghỉ hẳn học thêm

  • Hình ảnh thỏ con dễ thương

  • Hướng dẩn cách mở khóa khi bị chặn chức năng gửi tin nhắn.

  • Máy tính báo lỗi windows boot manager

  • x