Categories
Design

How to repeat specific rows or columns on every printed page in excel

Formatting documents in Word can be extremely frustrating.

Often Word completely disregards the ‘repeat header row’ setting – leaving you with a table that just doesn’t behave.

Sometimes, no matter what options are selected the ‘Repeat as header row at top of each page’ option doesn’t work as expected, if at all.

How to repeat specific rows or columns on every printed page in excel

The solution is simple, and quite baffling why it works where the other option does not.

  1. Select the table,
  2. at the top of the Window, under under ‘TABLE TOOLS’ open the ‘LAYOUT’ tab,
  3. click on the ‘Repeat Header Rows’ button.
  4. How to repeat specific rows or columns on every printed page in excel

For whatever reason, where the normal option fails to work this button some how fixes the table and makes the header row repeat across pages.

Help! What if this didn’t work!

Here’s a few tips if the above didn’t help.

Method 1: Text wrapping must be set to ‘None’

Repeatable headers DO NOT work with text wrapping enabled.

  1. Right click on your table and select ‘Table Properties’
  2. On the ‘Table’ tab set ‘Text Wrapping’ to ‘None’
  3. How to repeat specific rows or columns on every printed page in excel

Method 2: Page breaks must NOT be in table

If a page break is inside the table repeatable headers will not work.

To check for page breaks:

  1. Enable the display formatting option on the ‘Home’ tab, under ‘Paragraph’
  2. How to repeat specific rows or columns on every printed page in excel
  3. Look through the table for a page break and delete it, if there is one it will be at the bottom of the page (hence being a page break). You may also need to delete an empty line to join your table together again.
  4. How to repeat specific rows or columns on every printed page in excel

If you need the table to page break you will instead have to enable ‘Page break before’ on the table row.

  1. Click on the row you want to start on the new page
  2. Then on the ‘Layout’ tab open the ‘Paragraph’ settings window (click on the little arrow icon next to ‘Paragraph’)
  3. How to repeat specific rows or columns on every printed page in excel
  4. On the ‘Line and Page Breaks tab tick ‘Page break before’ and click ‘OK’ to save the changes
  5. How to repeat specific rows or columns on every printed page in excel

Method 3: Nested tables

Repeatable header rows will not work for nested tables, that is, a table inside a table – make sure you only have one table.

Whenever you are working with an Excel sheet, I am sure that the contents in it runs into multiple pages (when you print it that is). Now, since most such documents have a header row (which is the first row) it is always good to

. That way the header row would always be visible irrespective of how deep you scroll.

With soft copies that works absolutely fine. But, when you wish to print the document or convert it to PDF, for example, the header row prints only once. Whereas with data on multiple pages you would want the first row or column to appear at the top row of each page, right? We will learn how to make that happen, today.

Cool Tip: In case you are looking to print your company name, address, logo and akin on every page then I suggest that you add a page header and configure the settings as described in our detailed guide.

Setup Printing of Top Row on Every Excel Page

This setup applies to Office 2007 and later versions. I am not very sure about earlier ones but you may want to check it out.

Step 1: On your workbook, select the desired sheet and navigate to Page Layout tab on the ribbon. Then click on the icon for Page Titles placed under Page Setup section.

Step 2: On the Page Setup modal window, switch to the tab for Sheet and spot the section for Print titles. It hosts a row and a column option.

How to repeat specific rows or columns on every printed page in excel

Though we are focusing on header row, you must have understood that the setting can be applied to columns as well.

Step 3: To setup printing of top row on each page click on the button placed against Rows to repeat at top text box. For columns, take the second one.

Step 4: That will take you to the Excel sheet along with a dialog box. Click on row number 1 (on the sheet) and hit the dialog box right end button again.

Here, you can select multiple rows if you wish you repeat them on each page. Generally, you would want the top row and at rare circumstances the leftmost column.

Step 5: Back on the Page Setup modal window, you will see the text boxes populated with the row/column values to repeat on each page. You can go for a Print Preview, Print the contents or save the settings by hitting on Ok.

How to repeat specific rows or columns on every printed page in excel

Sometimes you also want to print the row headings reading 1, 2, . . . and column headings reading A, B, . . . If that’s what you want to activate then navigate to Page Layout -> Sheet Options and check the Print option placed under Headings.

You can do a sample print preview to check if the settings are working. Note that the settings are sheet specific and do not apply to the entire workbook.

Conclusion

I hope this solves your problem of keeping the header intact on each page of your document whether you print a hard copy or a soft copy (PDF, XPS, etc.) of the document. There are many more interesting print settings associated with Excel. Tell us if you need something very specific, we will try to help you out. Or if you are aware of a unique setting, share with us.

Last updated on 03 February, 2022

The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.

Read Next

  • How to repeat specific rows or columns on every printed page in excelTop 7 Android Keyboards with a Number Row Typing numbers along with words is a bit of a challenge on a software keyboard. Typically, you will long press on the letters to type…
  • How to repeat specific rows or columns on every printed page in excel5 Best iPhone Keyboards With Numbers Row on Top If you deal in numbers regularly, then iPhone’s onscreen keyboard is quite cumbersome. The act of switching back and forth between the alphabetic and numeric…
  • How to repeat specific rows or columns on every printed page in excel3 Amazing Battery Saving Apps for Every Android Device We have seen a lot of innovation when it comes to smartphone hardware specifications. Compared to last year’s dual core or quad core processor, we…
  • How to repeat specific rows or columns on every printed page in excelTop 3 VPN Services that Every Mac User Should Know About VPNs, or virtual private networks, enable you to browse the Internet while essentially protecting your identity. They work by masking your IP address from websites…
  • How to repeat specific rows or columns on every printed page in excel15 Most Useful Android Apps Every User Must Install As a hardcore Android blogger and YouTuber, there’s one question that I have always got from my family and friends and that is, “Bro please…
  • How to repeat specific rows or columns on every printed page in excelSecond Space From MIUI 8 is Needed on Every Android A few years ago, it was a one-phone one-SIM kind of a world we all lived in. I still remember how I used to carry…
  • How to repeat specific rows or columns on every printed page in excelTop 5 iMessage Apps That Every iPhone Must Have iOS 10 adds iMessage apps and unlocks an entirely new world on your phone specifically designed to improve upon the way we communicate. iMessage apps…
  • How to repeat specific rows or columns on every printed page in excel6 Awesome TV Shows Every Geek Should Watch Ah, weekends. The time when you kick back and put on Netflix and decide to chill. If you’re a geek, then now is a good…

DID YOU KNOW

The LG G Watch is the first smartwatch to be shipped with Android Wear.

More in

  • How to repeat specific rows or columns on every printed page in excel

10 Best Ways to Fix Mouse Cursor Moving on Its Own in Windows 10

How to repeat specific rows or columns on every printed page in excel

How to Disable Android Notification Sounds When Not Needed

JOIN THE NEWSLETTER

Get Guiding Tech articles delivered to your inbox.

If a worksheet spans more than one printed page, you can label data by adding row and column headings that will appear on each print page. These labels are also known as print titles.

Follow these steps to add Print Titles to a worksheet:

On the worksheet that you want to print, in the Page Layout tab, click Print Titles , in the Page Setup group.

How to repeat specific rows or columns on every printed page in excel

Note: The Print Titles command will appear dimmed if you are in cell editing mode, if a chart is selected on the same worksheet, or if you don’t have a printer installed. For more information about installing a printer, see finding and installing printer drivers for Windows Vista. Please note that Microsoft has discontinued support for Windows XP; check your printer manufacturer’s Web site for continued driver support.

On the Sheet tab, under Print titles, do one—or both—of the following:

In the Rows to repeat at top box, enter the reference of the rows that contain the column labels.

In the Columns to repeat at left box, enter the reference of the columns that contain the row labels.

For example, if you want to print column labels at the top of every printed page, you could type $1:$1 in the Rows to repeat at top box.
How to repeat specific rows or columns on every printed page in excel

Tip: You can also click the Collapse Popup Window buttons at the right end of the Rows to repeat at top and Columns to repeat at left boxes, and then select the title rows or columns that you want to repeat in the worksheet. After you finish selecting the title rows or columns, click the Collapse Dialog button again to return to the dialog box.

Note: If you have more than one worksheet selected, the Rows to repeat at top and Columns to repeat at left boxes are not available in the Page Setup dialog box. To cancel a selection of multiple worksheets, click any unselected worksheet. If no unselected sheet is visible, right-click the tab of a selected sheet, and then click Ungroup Sheets on the shortcut menu.

Lori Kaufman is a technology expert with 25 years of experience. She’s been a senior technical writer, worked as a programmer, and has even run her own multi-location business. Read more.

How to repeat specific rows or columns on every printed page in excel

If your worksheet takes up more than one page when printed, you can print row and column headings (also called print titles) on every page so your data is properly labeled, making it easier to view and follow your printed data.

Open the worksheet you want to print and click the “Page Layout” tab.

How to repeat specific rows or columns on every printed page in excel

In the “Page Setup” section, click “Print Titles”.

NOTE: The “Print Titles” button is grayed out if you are currently editing a cell, if you’ve selected a chart on the same worksheet, or if you don’t have a printer installed.

How to repeat specific rows or columns on every printed page in excel

On the “Page Setup” dialog box, make sure the “Sheet” tab is active. Enter the range for the rows you want to repeat on every page in the “Rows to repeat at top” edit box. For example, we want the first row of our spreadsheet to repeat on all pages, so we entered “$1:$1”. If you want more than one row to repeat, for example rows 1-3, you would enter “$1:$3”. If you want to have the first column, as an example, repeat on every printed page, enter “$A:$A”.

How to repeat specific rows or columns on every printed page in excel

You can also select the rows you want to repeat using the mouse. To do so, click the “Collapse Dialog” button on the right side of the “Rows to repeat at top” edit box.

The “Page Setup” dialog box shrinks to only show the “Rows to repeat at top” edit box.

How to repeat specific rows or columns on every printed page in excel

To select the rows you want to repeat, move the cursor over the row numbers until it turns into a right arrow then either click on the one row you want or click and drag over multiple rows. The row range is inserted into the “Rows to repeat at top” edit box automatically. To expand the “Page Setup” dialog box, click the “Collapse Dialog” button again.

Once you’ve defined the rows and columns you want to repeat, click “OK”.

How to repeat specific rows or columns on every printed page in excel

NOTE: If you have more than one worksheet selected in your workbook, the “Rows to repeat at top” and “Columns to repeat at left” boxes are grayed out and not available in the “Page Setup” dialog box. You must only have one worksheet selected. To unselect multiple worksheets, click on any other worksheet that is not selected. If all worksheets are selected, right-click on any of the selected sheets and select “Ungroup Sheets” on the popup menu.

How can we help you today?

How to repeat specific rows or columns on every printed page in Excel Print

Modified on: Wed, Nov 7, 2018 at 11:48 AM

If a worksheet is larger than one printed page, you can label data by adding row and column headings that will appear on each print page. These labels are also known as print titles.

Follow these steps to add print titles to a worksheet:

1. On the worksheet that you want to print, in the Page Layout tab, click Print Titles.

How to repeat specific rows or columns on every printed page in excel

2. In the Page Setup window, on the Sheet tab, under Print titles, do one—or both—of the following:

  • In the Rows to repeat at top box, enter the reference of the rows that contain the column labels, such as $1:$1.
  • In the Columns to repeat at left box, enter the reference of the columns that contain the row labels, such as $A:$F.

In the example below, only the top row of the workbook would appear at the top of every printed page. If you wanted to print the top two rows, you would enter $1:$2.
How to repeat specific rows or columns on every printed page in excel

Tip: You can also click the Collapse Popup Window buttons at the right end of the Rows to repeat at top and Columns to repeat at left boxes, and then select the title rows or columns that you want to repeat in the worksheet. After you finish selecting the title rows or columns, click the Collapse Dialog button again to return to the dialog box.

3. Click Print Preview to verify the results.

Usually, all data share the same header in a worksheet, if you print this worksheet, only the first page will include the header row. For making all printed out pages easy to read, you may need to keep the header row to repeat at top of them. How to achieve it? This article will recommend two methods for you:

You can keep certain row to repeat at top of every printed page of current worksheet with the Print Titles function in Excel. Please do as follows.

1. Click Page Layout > Print Titles. See screenshot:

2. In the Page Setup dialog box, click the button in the Rows to repeat at top box to select the row you need to repeat at top of every printed pages, and then click the OK button.

3. Click File > Print, you can see the specified row displays at the top of every printed pages.

The Split to Columns utility of Kutools for Excel help you to specify every n rows as a printed page in selected range, and keep the header row to repeat at top of every printed page in Excel.

Before applying Kutools for Excel, please download and install it firstly.

1. Click Kutools Plus > Split to Columns.

2. In the Split to Columns dialog box, you need to:

Note: If you enter 46 into the Rows per printed page box, every 46 rows of selected range will be printed on per page. And the selected title range will be added at the top of every page.

3. Then you will get a new worksheet with specified row display at the top of every printed page.

If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.

If you often print your work in Excel, you would benefit from knowing how to make sure a row (or multiple rows) print on every page that you print.

This is often needed as you want the data row headers to be visible so that it’s easier to know what a data point represents.

Thankfully, there is an in-built feature in Excel that allows you to set a row to print on every page (or set a column to print on every page).

In this tutorial, I will show you all the steps you need to do set the headers to show up on every printed page.

How to Set a Row to Print on Every Page in Excel

Suppose you have a dataset as shown below where there are hundreds of rows of data with the header in the first row.

If you print this data in the current form, you will only get the header on the first page and rest all the page will have only the data and not the headers.

Below are the steps to make sure the headers repeat on every printed page:

  1. Click on the Page Layout option in the ribbonHow to repeat specific rows or columns on every printed page in excel
  2. In the Page Setup group, click on the ‘Print Titles’ option. This will open the ‘Page Setup’ dialog boxHow to repeat specific rows or columns on every printed page in excel
  3. In the Page Setup dialog box, click on the cell selector icon (upward arrow) for the ‘Rows to repeat at top’ optionHow to repeat specific rows or columns on every printed page in excel
  4. In the dataset, select the header cells (you can also select the entire row if you want)How to repeat specific rows or columns on every printed page in excel
  5. Click OK

Now, when you print the data, you will have the selected header row cells repeat on every page.

You can use the same steps to also repeat multiple rows on each page (in case your headers occupy more than one row), as well as repeat one or more than one column on each page.

Note: It’s best to check the print preview before printing the worksheet. The print preview will show you exactly what will be printed on each sheet. To do this, click on the ‘File’ tab and then click on ‘Print’.

Also, it’s not necessary to always have the rows that you want to repeat at the top. For example, if you want to repeat row numbers 4 and 5, you can select these rows in step 4. Now, when Excel is printing your data, it will print the first page as expected, and from the second page onwards, it will have row #4 and #5 at the top, which will repeat on all pages.

I hope you found this Excel tutorial useful.

You may also like the following Excel tutorials:

  • Going to a particular cell
  • Moving from cell to cell
  • Moving from sheet to sheet
  • Inserting new sheets
  • Deleting sheets
  • Renaming sheets
  • Freezing rows and columns
  • Splitting the window
  • Print options
  • Selecting sheets to print
  • Printing rows or columns on every page
  • Defining a print range

If a sheet will be printed on multiple pages, certain rows or columns can be set up to repeat on each printed page.

As an example, if the top two rows of the sheet as well as column A need to be printed on all pages, do the following:

  1. Choose Format > Print Ranges > Edit Print Range.
  2. The Edit Print Ranges dialog (Figure 16) appears. Click on – none – to the left of the Rows to repeat area, and change it to – user defined –.
  3. In the text entry box in the center, type in the rows to repeat. For example, to repeat rows one and two, type $1:$2. (Or alternatively, click in cell A1 and drag to cell A2.)
  4. Columns can also repeat; click on – none – to the left of the Columns to repeat area, and change it to – user defined –.
  5. In the text entry box in the center, type in the columns to repeat. For example, to repeat column A, type $A. (Or alternatively, click in cell A1.)
  6. Click OK.

How to repeat specific rows or columns on every printed page in excel

As you have come to this site, probably you have just experienced this: Your worksheet spreads over more than one printed page and you want to set rows and columns to repeat. That way, the header row or column will be printed on each page. Large tables are much easier to read. But now you see the following: The buttons and fields in the Page Setup window are greyed-out so that you cannot select rows and columns. And there is no obvious reason for that. So, how to fix it?

The problem: Buttons for rows and columns to repeat are greyed-out

So, the problem is that the fields for setting the rows and columns to repeat are greyed-out. This occurs, when you just press Ctrl + P on the keyboard and navigate through the print preview window (as shown in the screenshot above).

The solution: Set the rows and columns via the “Page Layout” ribbon in Excel

The solution is both simple and not very obvious: Instead, you have to go through the “Page Layout” ribbon and set the print range there.

How to repeat specific rows or columns on every printed page in excelSet the rows and columns to repeat by clicking on “Print Titles” within the “Page Layout” ribbon.

  1. Click on “Print Titles” within the “Page Setup” section on the “Page Layout” ribbon.
  2. Select the rows or column you want to be repeated.
  3. Confirm by clicking on “OK”.

To be honest that is quite confusing and there seems no obvious reason for me to do it like this. But anyway, now you know how to solve this problem!

Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

How to repeat specific rows or columns on every printed page in excel

Add more than 120 great features to Excel!

Further reading

As it seems, far more people than expected still print out Excel sheets. At the same time, printing Excel sheets seems to be as troublesome as it always was.

That’s why I have created this: A comprehensive printing guide for printing Microsoft Excel sheets. Just click here to see the guide. You learn about all the frequently asked questions about printing in Excel.

Also interesting:

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

15 comments

your suggestion to open the print titles tab, bringing up a dialog box and then highlighting rows of the spreadsheet is crazy. You cannot highlight rows as long as the dialog is open and the line in the dialog about selecting the row is grayed out, so this is no solution. What did you mean? Can you explain better?

Thanks for your message. It’s not about highlighting rows and columns. It’s about defining rows and columns that should be repeated on every page – when you print an Excel file.

In order to set the rows and columns to repeat, you must go through the Page Setup function in Excel. And the dialog settings are greyed out if you just press Ctrl + Print and then Page Setup. If you go to the same window via the Page Layout ribbon like in the screenshots, it is not greyed out.

Is it clearer now?

Best regards,
Henrik

I’ve tried this and several other approaches and am still greyed out. I use this function for most spreadsheet and no issue, suddenly all week I can’t add in my headings to the print set up. Any ideas for when this solution doesn’t solve it? I’ve ready every suggestion I can find and no luck!

Hi Louise, does this problem only come up with one particular Excel file or also with different files?

This solution does not work. The “Rows to repeat at top” function is greyed out on all my new spreadsheets. It is only usable in old spreadsheets in which I had already set that option.

I suspect Microsoft played around with the code and somehow hosed this function on the latest release of Excel.

How to repeat specific rows or columns on every printed page in excel

If your worksheet contains multiple pages, you will see header rows or columns only printed in the first page by default. In this article, we are going to show you how to print header row in Excel to repeat on every printed page in Excel 2016, Excel 2013, Excel 2010 and Excel 2007.

By default, Excel does not print the headings of tables. You need to activate this feature from the Page Setup options. There are 2 ways to open the Page Setup window.

  1. From the top Ribbon
  2. From the Print window, just before printing

Let’s see these two methods more in detail.

Ribbon

When working on your workbook, begin by going into the Page Layout tab in the Ribbon. Then, click on the Print Titles icon to open the Page Setup dialog. The Sheet tab will already active. This menu contains the options to print header row in Excel to repeat on every printed page.

Page Setup dialog

The other option is a little longer than the Ribbon approach. First, open the Print page and go to the Page Setup window. You can access the Print page using one the following:

  1. Follow the File > Print path in the Ribbon
  2. Press Ctrl + P
  3. Press Ctrl + F2

On the Print window, click the Page Setup link to open the Page Setup dialog. You can find the link at the end of the Settings section.

How to repeat specific rows or columns on every printed page in excel

Once the dialog is open, go to the Sheet tab. You will see two reference inputs under the Print titles section.

How to repeat specific rows or columns on every printed page in excel

Print titles

Regardless of how you get to this step, you will need to be on the Sheet tab in the Page Setup dialog.

Click the reference selector icon corresponding to the heading type you want to be repeated. For example, to print the header row on each page, click the button and select the header row reference. In the example below, we set Rows to repeat at top to 2 nd row to repeat. If you want to repeat a column or columns, use the Columns to repeat at left option.

How to repeat specific rows or columns on every printed page in excel

Click OK to save your settings. From now on, every printed page will start with header row. You can remove the reference to take your changes back.

How to repeat specific rows or columns on every printed page in excel

It’s not uncommon for Google Sheets to contain large amounts of data that can span multiple printed pages. This can, however, become a problem for those reading your spreadsheet data as the columns can get a bit too difficult to follow along. Your target audience may struggle to correlate which data fits to which column and this is definitely something you’re going to want to avoid.

A solution is necessary and fortunately for you, there is a way to resolve this little obstacle. Google Sheets allows you to freeze the header columns (and rows) in place so that all titles are printed across each and every page. Needless to say, this makes identifying each column easier by having each subsequent page repeat the header found on the first page of the Google Sheet.

Freeze and Print Your Column Headers Easily On Google Sheets

As a personal preference, I recommend using Google Chrome for all related Google Drive needs as it tends to perform a bit better within the browser-based version of the app. It is up to you in the end as most browsers offer access to Google Drive and more importantly for this article, Google Sheets.

To freeze your header column titles in place:

  1. Navigate to Google Drive and open the spreadsheet in need of repeat header titles.
  2. In the ribbon at the top, click the “View” tab and from the drop-down menu select Freeze and then 1 column .
    How to repeat specific rows or columns on every printed page in excel
    If your cells happen to be merged, you may run into an error like this –
    How to repeat specific rows or columns on every printed page in excel
    – forcing you to make the necessary adjustments. If everything is fine, the display screen may offer up a modified version of the current spreadsheet. You’ll notice that the columns you chose to freeze follow along as you scroll to each subsequent column (A to Z). This just indicates that the column is locked in place. This is also true if your Sheet expands beyond Z and into the double or even triple letters.
  3. Next, swap to the “File” tab and select Print.

In the preview, you should notice each page is repeating the column titles at the top. If you wanted to do more than 1 column, they offer you 2 columns or Up to current column (?). The latter reaches out to the furthest column you have displayed. You can only freeze up to five columns or ten rows.

The same process can be done if you’re wanting to repeat rows as well, which can be found in the “View” tab. Just remember to avoid-
How to repeat specific rows or columns on every printed page in excel

and everything should be fine.

An Additional Way to Freeze Column Headers in Google Sheets

This next way to freeze your columns and rows is actually easier depending on how good you are at placing your cursor. Located in the top-left portion of the Google Sheet itself is a thick, gray bar that you can drag and drop for both rows and columns. This can greatly speed up the process for freezing columns (and rows) especially if you want to make adjustments.

In order to use this option:

  1. Place your cursor over the bar, hold down the left-click, and drag the bar right until you select the number of columns you want to freeze.
    How to repeat specific rows or columns on every printed page in excelHow to repeat specific rows or columns on every printed page in excel
  2. Plot the bar in place by releasing left-click.
    How to repeat specific rows or columns on every printed page in excel
  3. The same can be done for rows by drag and dropping the bar down until you reach the rows you want to freeze.
    How to repeat specific rows or columns on every printed page in excel
    Then, release.

Excel’s Print Titles enable you to print particular row and column headings on each page of the report. Print titles are important in multi-page reports where the columns and rows of related data spill over to other pages that no longer show the row and column headings on the first page.

Choose Page Layout> Page Setup> Print Titles. The Page Setup dialog box opens. On the Sheet tab, type a dollar sign ($) followed by the row numbers or column letters you want to print as titles in the Print Titles section. Entering $2:$2, excel repeats row 2 at the beginning of each page. Click ok.

Click the worksheet icon on the right to collapse the Page Setup dialog box so you can select the rows or columns you want to include. Click the button again to return to the Page Setup dialog box.

To clear print titles from a report if you no longer need them, open the Sheet tab of the Page Setup dialog box and then delete the row and column ranges from the Rows to Repeat at Top and the Columns to Repeat at Left text boxes. Click OK or press Enter.

Printing Gridlines and Row and Column Headings

The Sheet Options group contains two very useful Print check boxes (neither of which is automatically selected), the first in the Gridlines column and the second in the Headings column.

Choose Page Layout> Sheet Options and then choose from the following options:

Gridlines> Print:
Check this option to print the gridlines surrounding each cell in the worksheet.

Headings> Print:
Check this option to print the row numbers or column letters around the worksheet. You can see illustrates a worksheet printed with gridlines and row and column headings.

Select both check boxes (by clicking them to put check marks in them) when you want the printed version of your spreadsheet data to match as closely as their on-screen appearance. This is useful when you need to use the cell references on the printout to help you later locate the cells in the actual worksheet that need editing.

Selecting miscellaneous sheet printing options

Excel provides some worksheet print options that you might use occasionally. Follow these steps to access these options:

  1. Click the Page Layout tab on the Ribbon and click the dialog launcher button on the bottom right of the Sheet Options group container. Excel displays the Page Setup dialog box with the Sheet tab selected.
  2. Select among these options:

Black and White:
If you have a colorful worksheet but your printer is stuck in a monochrome world, you may discover that the worksheet colors don’t translate well to black and white. In this case, you can use this option to instruct Excel to ignore the colors while printing.

Draft Quality:
Printing in Draft mode doesn’t print embedded charts or drawing objects, cell gridlines, or borders. This mode usually reduces the printing time and is handy for getting a quick printout.

Comments:
If one or more cells in your worksheet have a cell comment, you can print these comments along with the worksheet by choosing an option from the Comments drop-down list. By default, comments are not printed.

Errors:
Excel gives you the option to print error values as they appear on your worksheet or to replace each of the error values with a predefined character in the printed output. Error values include #NUM!, #DIV/0!, #REF!, #N/A, #VALUE!, #NAME?, and #NULL!. Choose an option from the Cell Errors As drop-down list. By default, errors are printed as displayed.

Page Order:
If you have a worksheet that spans across several columns and down several rows, your worksheet is likely to end up with multiple vertical page breaks, horizontal page breaks, or both. In this case, you have the choice of printing your worksheet pages from left to right or from top to bottom. In the Page Order area, click the Down, Then Over radio button or the Over, Then Down radio button. By default, Excel prints down, then over.

Fergus

Well-known Member
  • Mar 10, 2004
  • #1
  • Excel Facts

    parry

    MrExcel MVP
    • Mar 10, 2004
  • #2
  • Hi Fergus, ideally you would like a repeat rows option but for the footer rather than at the top but unfortunately theres not an option for this. The following example code will place values of cells A55,A56 and A57 on a separate line in the footer.

    joelx

    New Member
    • Mar 10, 2004
  • #3
  • I’m looking for the same solution to repeating row at the bottom.

    Does the solution you give able to accomodate more than 255 characters as well as graphics?

    parry

    MrExcel MVP
    • Mar 10, 2004
  • #4
  • Hi Joel, I dont know the limitations of the footer. I would say you definitely wouldnt be able to fit 255 char on one line so you would need this to be split into several lines. Are you saying 255 char is in one cell or thats the combined text for several cells?

    All that this is doing is grabbing the values from the cells and placing it in the left hand footer, just as if you had manually typed it in. The VbLF bit of the code just means a line feed (pressing enter to go down another line) to separate the cell values. You could just manually copy and paste a value from a cell into the footer if you wanted.

    The other option I suppose is to have dummy sheets that repeats the last few rows on each page which is a bit of mucking around.

    When you use default Excel settings to print a multi-page Excel file that has row headers in the first column, readers will find it difficult to remember which information belongs to which row in the subsequent pages.

    To make it easy for your readers to follow which information belongs to which row, it is important to set the column that has row headers to repeat on the left side of each page. These repeating row headers are also known as print titles.

    When the print titles are set, the readers will be able to see which row headings or print titles apply to the information in the columns they see on the subsequent pages.

    Print a specific column on every printed page

    In this tutorial, I will show you how to locate the Page Setup menu in the Page Layout tab and define the column that you want to be repeated on the left side of each page.

    Follow these steps to add print titles or row headers to the left of each page of your Excel file.

    1. In the Excel file that you want to print, click the Page Layout tab at the top of the window.

    How to repeat specific rows or columns on every printed page in excel

    1. Click the Print Titles button on the Page Layout tab.

    How to repeat specific rows or columns on every printed page in excel

    1. On the Sheet tab In the Page Setup dialog box that appears, click on the Collapse Dialog button at the end of Columns to repeat at left box.

    How to repeat specific rows or columns on every printed page in excel

    The collapsed Page Setup – Columns to repeat at left dialog box will appear as follows.

    In this example, I have selected column A. See that the column reference $A:$A appears in the Columns to repeat at left dialog box.

    How to repeat specific rows or columns on every printed page in excel

    1. Click on the Collapse Dialog button again to go back to the full Page Setup dialog box.

    How to repeat specific rows or columns on every printed page in excel

    1. Click on the Print Preview button and scroll through a few pages to see what your printout will look like.

    How to repeat specific rows or columns on every printed page in excel

    Scroll through a few pages to see that the left column repeats at the left of every page.

    How to repeat specific rows or columns on every printed page in excel

    1. After confirming that the column repeats at the left of every page, go back to the Page Setup dialog box by clicking on the back arrow.

    How to repeat specific rows or columns on every printed page in excel

    1. Click OK at the bottom of the Page Setup dialog box to conclude the configuration process.

    How to repeat specific rows or columns on every printed page in excel

    Your spreadsheet file will now print with your selected column repeated on every page.

    More information on setting columns to repeat on every page in Excel

    In this tutorial, we have discussed how to repeat one column at the left of the Excel file, but you can follow the same steps to repeat many columns. You can click and drag to select many columns or you can input the values in the Columns to repeat at left box yourself.

    In our example, the reference $A:$A repeats the first column. Reference $A:$B would repeat the first two columns.

    report this ad

    This short guide is to show you how to repeat certain row headers or columns when printing. For those of us who spend ages getting a table “just right” on screen, then it goes pear shaped when you print it off 😉

    First off, in Excel 2007 :-

    Below is an example of a table that I want to print off – on screen it’s fine as I’ve frozen panes so the headers are always visible (if you want to know how to do that, click here)

    However, when I go to Print Preview it looks a mess – even after I’ve set the page layout to how I want it…

    Pages 2 and 3 of the preview don’t really show us what they correspond to, so how do we get the headers to repeat?

    Firstly go to Page Layout in the Ribbon and select Print Titles in the Page Setup section. If you hover over the Print Titles button, a description pops up to explain what it does…

    A dialogue box opens where you can choose the rows to repeat at top and/or columns to repeat at left…

    How to repeat specific rows or columns on every printed page in excel

    Click on the little box (technical term :)) on the right…

    How to repeat specific rows or columns on every printed page in excel

    Then choose the row you want to repeat – in this example we want to repeat the table headers so it will be row 3.

    Click back on the “small box” and it will take you back to the dialogue box, repeat the same method for choosing a column to repeat, for example column A, and click OK…

    How to repeat specific rows or columns on every printed page in excel

    You’ll not notice any difference on the actual spreadsheet, but if you now go back to print preview, you should see that the table header and the 1st column are now repeated on each page – below is page 3 in print preview…

    And that’s how you repeat rows / columns when printing 🙂

    Excel 2003

    In the 2003 version, the only difference is that you don’t have the ribbon. So, click on File on the toolbar, and go to Page Setup

    How to repeat specific rows or columns on every printed page in excel

    This will bring up the exact same dialogue box as in 2007, just make sure that the Sheet tab is selected, and follow the same steps as above…

    How to repeat specific rows or columns on every printed page in excel

    And you’ll have perfect looking printed tables 🙂

    APPLIES TO: вњ”пёЏВ MicrosoftВ ReportВ BuilderВ (SSRS) вњ”пёЏВ PowerВ BIВ ReportВ Builder вњ”пёЏВ ReportВ DesignerВ inВ SQLВ ServerВ DataВ Tools

    You can control whether to repeat row and column headers on every page of a paginated report for a tablix data region (a table, matrix, or list) that spans multiple pages.

    How you control the rows and columns depends on whether the tablix data region has group headers. When you click in a tablix data region that has group headers, a dotted line shows the tablix areas, as shown in the following figure:

    How to repeat specific rows or columns on every printed page in excel

    Row and column group headers are created automatically when you add groups by using the New Table or Matrix wizard or the New Chart wizard, by adding fields to the Grouping pane, or by using context menus. If the tablix data region has only a tablix body area and no group headers, the rows and columns are tablix members.

    For static members, you can display the top adjacent rows or the side adjacent columns on multiple pages.

    To display row headers on multiple pages

    Right-click the row, column, or corner handle of a tablix data region, and then click Tablix Properties.

    In Row Headers, select Repeat header rows on each page.

    Click OK.

    To display column headers on multiple pages

    Right-click the row, column, or corner handle of a tablix data region, and then click Tablix Properties.

    In Column Headers, select Repeat header columns on each page.

    Click OK.

    To display a static row or column on multiple pages

    On the design surface, click the row or column handle of the tablix data region to select it. The Grouping pane displays the row and column groups.

    On the right side of the Grouping pane, click the down arrow, and then click Advanced Mode. The Row Groups pane displays the hierarchical static and dynamic members for the row groups hierarchy and the Column groups pane shows a similar display for the column groups hierarchy.

    Click the static member that corresponds to the static member (row or column) that you want to remain visible while scrolling. The Properties pane displays the Tablix Member properties.

    If you don’t see the Properties pane, click the View tab at the top of the Report Builder window and then click Properties.

    In the Properties pane, set RepeatOnNewPage to True.

    Set KeepWithGroup to After.

    Repeat this for as many adjacent members as you want to repeat.

    Preview the report.

    As you view each page of the report that the tablix data region spans, the static tablix members repeat on each page.

    Post by Richarda44 » Tue Feb 22, 2011 11:39 am

    I do not use Page setup header for this.

    Instead use format / print range / edit / rows to repeat on every page

    remove normal page header if the rows provide sufficent header data so you don’t loose verticle space.

    OOo 4.1.1. bld 9593 on MS Windows 10 64 SP1 Chillblast fusion i5 and Acer Aspire E1-572 Laptop

    There’s got to be a better way
    And for all accountants – The change is coming

    Re: Page header (i.e. Row to repeat on every page)

    Post by nevergetany » Mon Jul 29, 2013 4:54 am

    On my version of OpenOffice (which I had to say was 3.1 to get registered ), I did what the instructions listed here said, and it worked. Remember it says to use “$” before your row number. That is confusing. If you have to it that way why not just list the row numbers you want repeated. EVEN BETTER, do it the same way you do it in writer, when you open the format, there is a box to check to repeat (only here you would have to put in the row numbers, and have another box for the columns).

    I read on the net that ORACLE gave OpenOffice to Apache. And I see that Apache has version 4 out. What has been the experience of others for version 4. Is it better than the oldder version 3.x? If you install version 4 do you have to remove version 3.x? or does it just overwrite the files it needs to overwrite, and maybe change the register vaues (and get rid of the obsolute ones). I really love my OpenOffice, it’s unfortunate they don’t put a full PDF editor in it as well. Maybe someday someone will undertake that project. While we are at it why not include a first rate scanner program too. Including a output file that has many types of files (like writer) and also has one that will output in DOC format. (That is DOC format, NOT WORD DOC format). Let me explain: A few weeks ago, I was given a membership list, that needed to be revised, and also get out of it a section that would produce Labels. (I still can not figure out how to make Mail Merge work with the SpreadSheet or Database, does anybody know?) So I scanned the list and saved it as a PDF. Now I was faced with the problem of how do I Import PDF into the SpreadSheet? I could not Copy and Paste from the PDF, it would not let me. So for several days I looked for a program that wold take my PDF and change it to DOC. I finally found a FREE online program that would do it. It converted my PDF into OpenOffice writer format. So now I could copy and paste. But was it ever tedious (this was my 1st time using any SpreadSheet and DataBase program). Things could of been less stressful and easier if I could scan and have the output in DOC format or if I could figure out how to import PDF into the SpreadSheet program. Getting the Labels to work with MailMerge is now giving me stress and frustrations. It would be helpful if in the Spreadsheet part for LABEL it could automatically do MailMerge for you. But Alas, life is not always easy.

    • Getting started
    • ↳ Survival guide
    • ↳ Beginners
    • ↳ Getting Started Guide for 3.x
    • ↳ Install, Setup and Troubleshooting
    • ↳ Admin Guide
    • ↳ MS Windows
    • ↳ Linux
    • ↳ Mac OSX
    • ↳ LibreOffice
    • ↳ Tutorials
    • ↳ Writer
    • ↳ Calc
    • ↳ Base
    • ↳ Database Examples
    • Applications
    • ↳ Writer
    • ↳ Writer Guide for 3.x
    • ↳ Writer Tutorials
    • ↳ Templates
    • ↳ Advanced Uses
    • ↳ Calc
    • ↳ Calc Guide for 3.x
    • ↳ Calc Functions
    • ↳ Calc Tutorials
    • ↳ Impress
    • ↳ Impress Guide for 3.x
    • ↳ Draw
    • ↳ Draw Guide for 3.x
    • ↳ Math
    • ↳ Math Guide for 3.x
    • ↳ Math Guide for 3.x
    • ↳ Base
    • ↳ Wiki
    • ↳ Base Tutorials
    • ↳ Tables & Queries
    • ↳ Forms
    • ↳ External Data Sources
    • ↳ Reporting
    • Customizing and Extending
    • ↳ Macros and UNO API
    • ↳ Basic Guide
    • ↳ OpenOffice Basic, Python, BeanShell, JavaScript
    • ↳ External Programs
    • ↳ UNO API and ODF
    • ↳ Code Snippets
    • ↳ Extension Repository
    • ↳ Macro Repository
    • ↳ Extensions
    • ↳ Extensions page
    • Community
    • ↳ General Discussion
    • ↳ Site Feedback
    • ↳ Forum Governance
    • ↳ User Experience (UX)
    • ↳ Announcements
    • Business
    • ↳ Institutions & Educational
    • ↳ Paid support
    • ↳ Consultants Directory
    • Board index
    • All times are UTC+02:00
    • Delete cookies
    • Contact us

    Powered by phpBB® Forum Software © phpBB Limited

    For example, when you print an Excel spreadsheet on several pages, you can’t see column headers or labels on the second and following pages:

    How to repeat specific rows or columns on every printed page in excel

    To print column headers or labels on every page, do the following:

    1. On the Page Layout tab, in the Page Setup group, click Print Titles:

    2. In the Page Setup dialog box, on the Sheet tab, in the Print titles section:

    How to repeat specific rows or columns on every printed page in excel

    • choose rows you want to print on every page in the Rows to repeat at top field;
    • choose columns you want to print on every page in the Columns to repeat at left field.

    For example, for the selected rows $1:$4, you will get:

    How to repeat specific rows or columns on every printed page in excel

    Notes:

      If you want to print row and column labels, you can check the Row and column headings checkbox:

    How to repeat specific rows or columns on every printed page in excel

    How to repeat specific rows or columns on every printed page in excel

    How to repeat specific rows or columns on every printed page in excel

    How to repeat specific rows or columns on every printed page in excel

    How to print a large Excel spreadsheet

    How to repeat specific rows or columns on every printed page in excel

    Three different ways for printing a selected area

    How to repeat specific rows or columns on every printed page in excel

    Adding a header and footer to the worksheet

    • Facebook
    • Twitter
    • Pinterest
    • Privacy
    • Terms
    • Contact

    We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

    I have an SSRS report, for some reason the header is not repeating on all of the pages in the print preview mode. Below is the image of the table:How to repeat specific rows or columns on every printed page in excel

    I have as well checked the Repeat Header rows on each page option as below:

    How to repeat specific rows or columns on every printed page in excel

    I would like the headers to repeat on every page.

    How to repeat specific rows or columns on every printed page in excel

    How to repeat specific rows or columns on every printed page in excel

    2 Answers 2

    To repeat rows with column headings for a table with row groups

    In Design view, select the table. The Grouping pane displays the row groups.

    On right side of the Grouping pane, click the down arrow, and then click Advanced. The Grouping pane displays static and dynamic tablix members for each group. You can only set properties on a static tablix member.

    In the Row Groups pane, click the static tablix member for the row that you want to repeat. When you select a static tablix member, the corresponding cell on the design surface is selected, if there is one. The Properties pane displays the properties for the selected tablix member.

    • Set the KeepWithGroup property in the following way:
    • For a static row that is above a group, click After.
    • For a static row that is below a group, click Before.
    • Set the RepeatOnNewPage property to True.

    Alan Murray has worked as an Excel trainer and consultant for twenty years. On most days, he can be found teaching Excel in a classroom or seminar. Alan gets a buzz from helping people improve their productivity and working lives with Excel. Read more.

    How to repeat specific rows or columns on every printed page in excel

    Sometimes you want to print only a specific selection of cells on a worksheet. But when you print in Microsoft Excel, all data on a worksheet is printed by default. This article will show two ways to print a selected range.

    Printing a Specific Selection of Cells in Microsoft Excel

    This first option is the quickest method to print a selected range of cells.

    Select and highlight the range of cells you want to print.

    How to repeat specific rows or columns on every printed page in excel

    Next, click File > Print or press Ctrl+P to view the print settings.

    How to repeat specific rows or columns on every printed page in excel

    Click the list arrow for the print area settings and then select the “Print Selection” option.

    How to repeat specific rows or columns on every printed page in excel

    The preview will now show only the selected area. Click “Print” to finish the process.

    Setting the Print Area in Microsoft Excel

    If you print the selected area frequently, you can set the selected range as the print area. This option will save you from needing to select the range each time you print.

    Select the range of cells you want to set as the print area. Next, click Page Layout > Print Area > Set Print Area.

    How to repeat specific rows or columns on every printed page in excel

    The print area is now set.

    The next time you print (File > Print or press Ctrl+P), this area will be printed by default.

    To remove a print area, click Page Layout > Print Area > Clear Print Area.

    Overview: A few tips for getting the column headings to display at the top of each page of a SQL Server Reporting Services (SSRS) report. SQL Server 2008 R2 was used for all examples.

    Using the Advanced Properties

    This entry is to expand on my previous blog about Repeating Column Headings which discusses the use of Advanced Tablix Member Properties to repeat column headings on each page. If you haven’t used the “RepeatOnNewPage” property too much, it can seem like sometimes it works & sometimes it doesn’t. It can definitely be a bit frustrating.

    Before we go into a few examples, I want to mention the Tablix Properties. These are great when working with a Matrix; however, they have no affect with a regular Table data region.

    Rather than using the above Tablix Properties, since we have a Table we’re going to be using the Advanced Tablix Member Properties. The Advanced Tablix Member Properties become available when you click the little arrow on the right side of the grouping pane.

    How to repeat specific rows or columns on every printed page in excel

    Case 1: No Row Groups – Column Headings in a Header Row – Works!

    This report has one Details group, but no parent groups. Notice the column headings are located in a header row of the table. The RepeatOnNewPage property works perfectly in this type of layout (i.e., no row groups).

    Case 2: One Row Group – Column Headings in a Header Row – Repeating Does NOT Work

    In this layout I moved the Product to be a parent group of the Details. I left the column headings like they were in the previous example: in a header row of the table. Although I set the same advanced tablix member properties, the column headings do NOT repeat on the following page.

    Why, you ask? Read on to Case 3.

    Case 3: One Row Group – Column Headings Within the Group – Works…Maybe

    This layout moves the column headings to be within the row grouping (see how they are within the bracketed rows, rather than one row on top (like they were in Case 2).

    This technique works, but since the headings repeat within every single group, it might or might not work for your real life situation.

    Case 4: Using a Fake Group – Row Headings Within the Group – Works Great!

    This last layout improves on Case 3 by creating a fake group. This fake group is so we can keep the Column Headings within a row group, yet not have them repeat for every product that changes.

    The group is set up like this; it really doesn’t group on anything:

    The column headings now only appear once, at the top of every page. Perfect!

    Finding More Information

    There’s a key concept discussed at this MSDN article: How to Display Row and Column Headers on Multiple Pages (Reporting Services). It’s this comment that inspired me to use the “fake group” approach discussed in Case 4.

    “For a table, the row that contains column names is controlled by the tablix member in a row group. Set tablix member properties in the Properties pane.”

    LinkBack
    • LinkBack URL
    • About LinkBacks
  • Thread Tools
    • Show Printable Version
    • Subscribe to this Thread…
    • Mark this thread as unsolved…
  • Rate This Thread
    Display
    • Linear Mode
    • Switch to Hybrid Mode
    • Switch to Threaded Mode
  • Print column headers on select pages only

    I want to be able to print column headers on many pages of a document, but
    NOT on ALL of them.

    RE: Print column headers on select pages only

    Sorry – it’s an all or none option, unless you print those you want with the
    header, delete the header, and then print the ones you want WITHOUT the header

    “RonJ” wrote:

    > I want to be able to print column headers on many pages of a document, but
    > NOT on ALL of them.

    Re: Print column headers on select pages only

    Maybe you could use a macro that prints each page the way you want.

    You could record a macro while you print the first page (add the header). Then
    remove the header/rows to repeat at top, print the next, continue until you’re
    done.

    Then just execute that macro when you want to print again.

    RonJ wrote:
    >
    > I want to be able to print column headers on many pages of a document, but
    > NOT on ALL of them.

    Re: Print column headers on select pages only

    You can print the pages you want with the column headers by selecting the pages to print
    ie: Print pages 1-4, with the header column selected
    de-select the header column print option (blank) and print only the page you don’t
    want the header on ie; print page 5

    You can use print preview to see what pages are going to print and what they are going to look like
    you can also insert page breaks where you want.

    To print the header row at top repeatedly in each page is usually used in our working, but have you ever tried to print title rows repeatedly in multiple sheets? In this article, I have two easy ways to handle this question in Excel.

    There is no built-in feature to repeatedly print titles in multiple sheets, but you can use a macro to handle it.

    1. Activate the first sheet in your workbook, click the anchor at the bottom-right corner of Page Setup group on the Page Layout tab. See screenshot:

    2. In the Page Setup dialog, click Sheet tab, select the title row you want to repeatedly print from Rows to repeat at top, and click OK. See screenshot:

    3. Press Alt + F11 keys to enable the Microsoft Visual Basic for Applications window, click Insert > Module to create a new Module.

    4. Then paste below code to the blank script, and press F5 to run the code.

    VBA: Repeatedly print titles in multiple sheets

    After applying the VBA, the top row in each sheet will be repeated at top of corresponding sheet while printing.

    If you have installed Kutools for Excel, you can apply its Copy Page Setup to quickly copy all or specific page setup from one sheet to other sheets in a workbook.

    After installing Kutools for Excel, please do as below: (Free Download Kutools for Excel Now!)

    1. Select one sheet, for instance, select Sheet1, and click the anchor at the bottom-right corner of Page Setup group on the Page Layout tab. See screenshot:

    2. In the Page Setup dialog, click Sheet tab, and select the titles you want to repeatedly print from Rows to repeat at top. See screenshot:

    3. Click OK, then click Kutools Plus > Printing > Copy Page Setup. See screenshot:

    4. In the Copy Page Setup dialog, check the sheet names you want to apply the same page setup in Copy to list, then check Rows to repeat at top in the Options list. See screenshot:

    5. Click Ok. Now the top rows have been repeated at the top across multiple sheets while printing.

    How to repeat specific rows or columns on every printed page in excel

    We often want to copy and paste, or replicate, cell data into other worksheet cells, but how can we do it quickly? By using a very powerful feature of Microsoft Excel called autofill.

    This comprehensive tutorial shows the Excel user how to autofill adjacent cells, selected cells that are not adjacent, or a cell range.

    Tutorial Sections

    • Autofill Adjacent Column or Row Cells
    • Autofill Selected Cells that are Non-Adjacent
    • Autofill a Cell Range

    How to Autofill a Column or Row (Adjacent Cells)

    There are two fast ways that an Excel user can fill adjacent cells in a column or row with the same numerical or text data. This section shows both methods.

    Method #1: Ctrl + D

    1. Type the data into the a worksheet cell and Enter.
    2. Click in the cell with the data and, keeping the left mouse button pressed, drag to select the rest of the cells in the row or column that you would like autofilled.
    3. Release the mouse button.
    4. Press Ctrl + D (the Ctrl key is held while the D key is pressed) and the cells are filled.

    Method #2: Using the Fill Handle

    The Fill Handle is a powerful Excel tool for autofilling a linear series, a growth series, and many other types of data.

    The Fill Handle can also be used to autofill the same value AS LONG AS the value isn’t a series starter. If so, Method #1 must be used. Examples of series include days of the week, month names, series involving dates, and time.

    1. Type the data into a spreadsheet cell.
    2. Place the cursor in the bottom right corner of the cell you just typed in until you see a plus sign. With the left mouse button, press and drag the Fill Handle (plus sign) to highlight all of the cells you want filled.
    3. Release the mouse button and the cells are filled with the value typed in the first cell.

    Autofill Selected Cells that are Non-Adjacent

    Sometimes we want to autofill non-contiguous cells in a row or column. For example, we may have an empty row between each data row for readability, but now want to autofill part of a column. Use the technique below to autofill individually selected worksheet cells.

    How to repeat specific rows or columns on every printed page in excel

    1. Select the individual cells you want autofilled by pressing and holding the Ctrl key as you click inside the cells. Figure 1 shows that we have selected cells A1, A3, A5, and A7.
    2. After clicking in the last cell, type the number or text value you want autofilled in the last cell (figure 2).
    3. Press Ctrl + Enter key and the selected cells will be autofilled (figure 3).

    How to Autofill a Cell Range with the Same Data

    The methods in the first section of this tutorial do not work if you wish to autofill a range of cells that covers multiple columns and rows (2-dimensional). Use the technique described below.

    How to repeat specific rows or columns on every printed page in excel

    1. Select the range of cells you wish to autofill by pressing and holding the left mouse button while dragging the cursor (figure 1).
    2. Type the numerical or text value you wish to autofill (figure 2).
    3. Press Ctrl + Enter key and the selected cells will be autofilled with the value you typed (figure 3).

    In Excel, we can quickly hide columns in one worksheet, but, have you ever tried to hide columns across multiple sheets at once? Normally, you may try to select all sheets and apply the Hide feature, but it will not work successfully. This article, I will talk about how to hide columns across multiple worksheets quickly?

    Hide columns on multiple worksheets in a workbook with VBA code

    The following code can help you to hide specific columns across all worksheets immediately, please do as this:

    1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

    2. Click Insert > Module, and paste the following code in the Module Window.

    VBA code: Hide columns on multiple sheets at once:

    3. Then press F5 key to run this code, and a prompt box is popped out to remind you type the columns that you want to hide across all worksheets, see screenshot:

    4. And then click OK button, your specified columns have been hidden in all worksheets at once.

    Tech Tip: Printing in Excel – Scaling and Repeating Rows and Columns

    How to repeat specific rows or columns on every printed page in excel

    Last month we talked about ways you can adjust Excel printing by setting page breaks and print areas in Excel . This month, we’re learn more about Excel printing with page scaling and row/column repeating . Read on for all the details.

    Page Scaling: If your worksheet has a lot of columns or rows, you can use Excel’s Scale to Fit options to reduce the size of things to better fit your print job. How do you do this?

    1. Click the Page Layout tab on the ribbon.
    2. In the Scale to Fit group, in the Width box, select 1 page, and in the Height box, select Automatic. Columns will now appear on one page, but the rows may extend to more than one page.
      How to repeat specific rows or columns on every printed page in excel

    To print your worksheet on just one page, in the Height box, select 1 page. Do note that if you do this, the resulting printout may be hard to read, because Excel shrinks the data to fit the sheet of paper. To see how much scaling (by percentage) is used, look at the number in the Scale box. If it’s a low number, you may need to make other adjustments before you print. For example, you may need to change the page orientation from portrait to landscape or target a larger paper size. For more information, see the section below to understand a few things about scaling a worksheet to fit a printed page.

  • To print your worksheet, press CTRL+P to open the Print dialog box, and then click OK.
  • If things don’t come out like you expect, there are some quirks/tweaks you may need to adjust. This Microsoft Support article goes over some other things you need to know.

    Repeating Columns or Rows: If you have a larger spreadsheet, you may want to have columns or rows repeat on each page to make things easier to read.

    1. Select the worksheet that you want to print.
    2. On the Page Layout tab, in the Page Setup group, click Print Titles.
    3. On the Sheet tab, under Print titles, do one or both of the following:
      • In the Rows to repeat at top box, type the reference of the rows that contain the column labels, or click on the on the collapse dialog selection button and select the rows you want repeated.
      • In the Columns to repeat at left box, type the reference of the columns that contain the row labels, or click on the on the collapse dialog selection button and select the columns you want repeated.

    How to repeat specific rows or columns on every printed page in excel
    For example, if you want to print column labels at the top of every printed page, you could type $1:$1 in the Rows to repeat at top box.

    If you’re looking for more Excel printing tips, Microsoft has a lot more tips to help you.

    For example, when you print an Excel spreadsheet on several pages, you can’t see column headers or labels on the second and following pages:

    How to repeat specific rows or columns on every printed page in excel

    To print column headers or labels on every page, do the following:

    1. On the Page Layout tab, in the Page Setup group, click Print Titles:

    2. In the Page Setup dialog box, on the Sheet tab, in the Print titles section:

    How to repeat specific rows or columns on every printed page in excel

    • choose rows you want to print on every page in the Rows to repeat at top field;
    • choose columns you want to print on every page in the Columns to repeat at left field.

    For example, for the selected rows $1:$4, you will get:

    How to repeat specific rows or columns on every printed page in excel

    Notes:

      If you want to print row and column labels, you can check the Row and column headings checkbox:

    How to repeat specific rows or columns on every printed page in excel

    How to repeat specific rows or columns on every printed page in excel

    How to repeat specific rows or columns on every printed page in excel

    How to repeat specific rows or columns on every printed page in excel

    How to print a large Excel spreadsheet

    How to repeat specific rows or columns on every printed page in excel

    Three different ways for printing a selected area

    How to repeat specific rows or columns on every printed page in excel

    Adding a header and footer to the worksheet

    • Facebook
    • Twitter
    • Pinterest
    • Privacy
    • Terms
    • Contact

    We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

    If you are working with a spreadsheet/worksheet that takes more than one page when printed, you may want to repeat rows at the bottom and columns at the right of each page.

    For example, if you need to show the final grade scored by a student in a class (inclusive of all the subject grades), you need to repeat columns on each page’s right.

    Or there are times when you want to show a student’s total monthly attendance in a class (at the right of each printed page along with a legend row at the bottom). You’ll need to repeat the rows at the bottom and columns at the right of each printed page, and because it will print to PDF, the worksheet will break into different pages.

    How to repeat specific rows or columns on every printed page in excel

    MS Excel does not have a direct way of repeating rows at the bottom or repeating columns at the right while printing. It has options to only repeat rows or columns at the top or left. One of the ways to repeat rows at the bottom of a page is to use a footer on every page. However, the use of a footer is limited specifically for large texts.

    GrapeCity Documents for Excel (GcExcel) introduces new features “Repeating Rows at Bottom” and “Repeating Columns on Right” on each printed page of a PDF file.

    GcExcel workbook’s PageSetup API “PrintTailRows” & “PrintTailColumns” will help to provide a direct way for programmers to repeat rows at the bottom or repeat columns at the right of each printed page. For details, please see the help topics Configure Columns to Repeat at Left and Right and Configure Rows to Repeat at Top and Bottom.

    This article describes how to create a “Monthly Class Attendance” report with several rows and columns. The “Monthly Class Attendance” report will be printed into a three-page PDF file. Since all of the columns won’t get printed on a single PDF page, there is a need to repeat certain columns (Total attendance of a student in a month) at the right of each printed page.

    Similarly, since the abbreviated letters T,U,E,P are used in every row, the user needs to see this abbreviation at the bottom of every printed page. We will accomplish this task with the help of PageSetup APIs “PrintTailRows” & “PrintTailColumns.”

    Repeating bottom and right rows and columns and converting Excel spreadsheets to PDFs in .NET Core

    Step 1: Load the Excel file in a workbook and fill in the appropriate data

    Step 2: Set appropriate PrintTailColumns & PrintTailRows properties along with other properties

    Step 3: Export to PDF

    Here are images showing the three-page output with the last row repeated at the bottom of each page and column totals repeated at the right side of each page:

    How to repeat specific rows or columns on every printed page in excelHow to repeat specific rows or columns on every printed page in excelHow to repeat specific rows or columns on every printed page in excel

    Download the sample here to follow along. If you have any questions about the new features, please leave them in the comments below.

    If there’s too much data in an Excel spreadsheet, it will inevitably be divided into several pages when printing. And it might be difficult to figure out the corresponding meaning or description of each value. The best way to avoid this kind of trouble is to repeat a print title on each page.

    1. First, go to Page Layout tab, and click Print Titles in Page Setup group.

    2. A Page Setup dialog box will display, select Sheet and hit the red button in the box of Rows to repeat at top.

    How to repeat specific rows or columns on every printed page in excel

    3. Now you will see a Page Setup – Rows to repeat at top popup window, select the print title which you want to repeat, usually the first row of the table. The values are automatically filled into the textbox, for this example, it is S1:S1. Then hit the red button again.

    4. You will go back to the last dialog box, the values still remain, click OK and you have set Glass-Male-Female as the print title and it will be repeated on each page.

    To add column headings to each page, select the row or rows that you want to repeat and then do one of the following:

    How to repeat specific rows or columns on every printed page in excel

    • Under Table Tools, on the Layout tab, in the Data group, click Repeat Header Rows:

    How to repeat specific rows or columns on every printed page in excel

    If you change a table heading on the first page, the table headings on all other pages change as well. And you won’t be able to modify any, but the first header row – the automatically generated rows are locked.

    How to repeat specific rows or columns on every printed page in excel

    Note: These rows repeat only when that table is split by a soft page break (one that is inserted by the system). If you insert a hard page break in the table (by pressing Ctrl+Enter), you are, in effect, breaking the table into two, so the heading is not repeated automatically.

    How to repeat specific rows or columns on every printed page in excel

    How to keep a row of the table on one page in a Word document

    How to repeat specific rows or columns on every printed page in excel

    How to put each Heading 1 on the new page

    How to repeat specific rows or columns on every printed page in excel

    How to create different headers and footers on odd and even pages

    • Facebook
    • Twitter
    • Pinterest
    • Privacy
    • Terms
    • Contact

    We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

    If you have got a table that extends over several pages in a Word document, it’s useful to be able to repeat the header row at the top of each page automatically, so that it stays there no matter what you change in the table itself. This article shows you how to do that in Word 2007 and Word 2010 (we use the same procedure for both)

    Why would I want to repeat my header row on multiple pages?

    If you’re presenting a table which contains fairly self-explanatory information, for example, name, surname, book title, year, then you probably don’t need to repeat the heading row, even if the table runs across more than one page. But if you think that the person who will consult the table will need to keep reminding themselves about what the different columns contain, it’s useful to add the header row at the top of each page.

    Doing this automatically rather than manually inserting a new row into each page of the table ensures that however much the table changes, the header row will stay at the top of each page.

    How do I repeat the header row in Word?

    We’re going to use the example of a list of books I have read. Here’s the table:

    How to repeat specific rows or columns on every printed page in excel

    Now, this is a long list, and it goes over more than one page. OK, it’s fairly self-explanatory, but I might forget what the Acquired and Read headers are.

    At the moment, when the table goes onto the next page of the document, the table just carries on, with no header rows on the second and third pages:

    How to repeat specific rows or columns on every printed page in excel

    Please NOTE that we’re in Print Layout view on the View menu at the moment: when we repeat the headers, the repeat is only visible in Print Layout or Print Preview, both of which show you what your document will look like when it’s printed. Word defaults to Print Layout view, but check, just in case:

    How to repeat specific rows or columns on every printed page in excel

    Select the header row of your table so that you can tell Word that this is the header row by left-clicking with your cursor to the left-hand side of that row:

    Now, because you’ve got a table in your document, Word will have added the Table menu tabs to your ribbon. There are two: Layout and Design. Choose the Layout tab. Find the Repeat Header Rows button:

    How to repeat specific rows or columns on every printed page in excel

    Click on the Repeat Header Rows button. Like magic, if you scroll down the page, you will now see that your header row is repeated!

    How to repeat specific rows or columns on every printed page in excel

    Note: if you don’t have a row selected, the Repeat Header Row button will be greyed out and you can’t press it.

    Here’s the magical thing: you can of course do this manually by inserting a row at the top of each page of your table. But then, if you move the text of the table around or resize it, you risk your manual header row not being the top row of your page.

    Using the automatic function means that, whatever you do to your table, the top row of a new page will always be the header row (UNLESS you force a manual page break).

    Here, I’ve changed the text size to make it larger. You can see that the first entry on the second page is no longer Coleridge, but the header row is still in place:

    How to repeat specific rows or columns on every printed page in excel

    How do I repeat multiple header rows in Word?

    You can display multiple header rows in Word in the same way. Make sure that you highlight BOTH rows that you want to repeat, and press the Repeat Header Rows button as before:

    How to repeat specific rows or columns on every printed page in excel

    And there you go: the first two lines of the table repeat on each page:

    How to repeat specific rows or columns on every printed page in excel

    How to repeat header rows in Word 2003

    In Word 2003, you will need to use menus rather than the ribbon.

    Highlight the header row of your table.

    Select the Table menu and click Heading Rows Repeat.

    Today we’ve learned how to make the header rows repeat in a Word document. This is part of a series on Tables which I’ll be writing and publishing over the next few weeks.

    Please note, these hints work with versions of Microsoft Word currently in use – Word 2003, Word 2007 and Word 2010, all for PC. Mac compatible versions of Word should have similar options. Always save a copy of your document before manipulating it. I bear no responsibility for any pickles you might get yourself into!

    Excel 2013 For Dummies

    Excel 2013’s Print Titles feature enables you to print particular row and column headings on each page of the report. Print titles are important in multipage reports where the columns and rows of related data spill over to other pages that no longer show the row and column headings on the first page.

    Don’t confuse print titles with the header of a report. Even though both are printed on each page, header information prints in the top margin of the report; print titles always appear in the body of the report — at the top, in the case of rows used as print titles, and on the left, in the case of columns.

    To designate rows and/or columns as the print titles for a report, follow these steps:

    Click the Print Titles button on the Page Layout tab on the Ribbon or press Alt+PI.

    The Page Setup dialog box appears with the Sheet tab selected.

    To designate worksheet rows as print titles, go to Step 2a. To designate worksheet columns as print titles, go to Step 2b.

    Click in the Rows to Repeat at Top text box and then drag through the rows with information you want to appear at the top of each page in the worksheet below. If necessary, reduce the Page Setup dialog box to just the Rows to Repeat at Top text box by clicking the text box’s Collapse/Expand button.

    Below, the Collapse/Expand button was clicked associated with the Rows to Repeat at Top text box and then dragged through rows 1 and 2 in column A of the Little Bo-Peep Pet Detectives – Client List worksheet. Excel entered the row range $1:$2 in the Rows to Repeat at Top text box.

    Excel indicates the print-title rows in the worksheet by placing a dotted line (that moves like a marquee) on the border between the titles and the information in the body of the report.

    How to repeat specific rows or columns on every printed page in excel

    Click in the Columns to Repeat at Left text box and then drag through the range of columns with the information you want to appear at the left edge of each page of the printed report in the worksheet below. If necessary, reduce the Page Setup dialog box to just the Columns to Repeat at Left text box by clicking the text box’s Collapse/Expand button.

    Excel indicates the print-title columns in the worksheet by placing a dotted line (that moves like a marquee) on the border between the titles and the information in the body of the report.

    Click OK or press Enter to close the Page Setup dialog box.

    The dotted line showing the border of the row and/or column titles disappears from the worksheet.

    Rows 1 and 2 containing the worksheet title and column headings for the Little Bo-Peep Pet Detectives client database are designated as the print titles for the report in the Page Setup dialog box. Here, you can see the Print Preview window with the second page of the report. Note how these print titles appear on all pages of the report.

    How to repeat specific rows or columns on every printed page in excel

    To clear print titles from a report if you no longer need them, open the Sheet tab of the Page Setup dialog box and then delete the row and column ranges from the Rows to Repeat at Top and the Columns to Repeat at Left text boxes. Click OK or press Enter.

    About This Article

    This article is from the book:

    About the book author:

    Greg Harvey, PhD, is President of Mind Over Media, an online media company. He has written all editions of Excel For Dummies, Excel All-in-One For Dummies, and Excel Workbook For Dummies. Greg is an experienced educator with a wide variety of interests.

    Learn more about Advanced Report Layouts.

    This section describes the following tasks for advanced report layouts:

    Creating Batch Reports

    It is a common requirement to print a batch of documents, such as invoices or purchase orders in a single PDF file. Because these documents are intended for different customers, each document requires that the page numbering be reset and that page totals are specific to the document. If the header and footer display fields from the data (such as customer name), then these must be reset as well.

    BI Publisher supports this requirement through the use of a context command. This command allows you to define elements of the report to a specific section. When the section changes, these elements are reset.

    The following example demonstrates how to reset the header and footer and page numbering within an output file:

    The following XML code is a report that contains multiple invoices:

    Each G_INVOICE element contains an invoice for a potentially different customer. To instruct BI Publisher to start a new section for each occurrence of the G_INVOICE element, add the @section command to the opening for-each statement for the group, using the following syntax:

    where group_name is the name of the element for which you want to begin a new section.

    For example, the for-each grouping statement for this example is as follows:

    The closing tag is not changed.

    The following figure shows a sample template for batch reports:

    The G_INVOICE group for-each declaration is still within the body of the report, even though the headers are reset by the command.

    The following table describes the values of the form fields from the template in the previous figure (that shows a sample template for batch reports):

    Begins the G_INVOICE group, and defines the element as a Section. For each occurrence of G_INVOICE, a new section is started.

    Microsoft Word does not support form fields in the header, therefore the placeholder syntax for the TRX_NUMBER element is placed directly in the template.

    Closes the G_INVOICE group.

    Now for each new occurrence of the G_INVOICE element, a new section begins. The page numbers restart, and if header or footer information is derived from the data, it is reset as well.

    Handling No Data Found Conditions

    When you use @section with the BI Publisher commands for-each or for-each-group (for example: ), and the input data file has no data, then an empty or invalid PDF output document may be generated for that for-each loop. To prevent this from happening, edit the RTF template.

    1. At the end of the RTF template, add a section break.
    2. On the last page (the new section page), add the command No Data Found

    where ELEMENT_NAME is the same data element that you are using in the [email protected] loop.

    Now if no data exists for ELEMENT_NAME, a valid PDF is generated with the text “No Data Found” .

    Inserting Pivot Tables

    The columns of a pivot table are data dependent.

    At design time you do not know how many columns are reported, or what the appropriate column headings are. Moreover, if the columns should break onto a second page, you must be able to define the row label columns to repeat onto subsequent pages. The following example shows how to design a simple pivot tale report that supports these features. See Inserting a Pivot Table.

    This example uses the following XML sample:

    From this XML code, a report is generated that shows each industry and totals the sales by year as shown in the following illustration.

    The following illustration shows the template to generate the report that is shown in the previous illustration.

    The form fields in the template that is shown in the previous illustration have the values that are described in the following table.

    Defines the first column as a header that should repeat if the table breaks across pages. See Defining Columns to Repeat Across Pages.

    Uses the regrouping syntax (see Regrouping the XML Data) to group the data by YEAR; and the @column context command to create a table column for each group (YEAR). See Controlling the Placement of Instructions Using the Context Commands.

    Placeholder for the YEAR element.

    Closes the for-each-group loop.

    Begins the group to create a table row for each INDUSTRY.

    Placeholder for the INDUSTRY element.

    Uses the regrouping syntax (see Regrouping the XML Data) to group the data by YEAR; and the @cell context command to create a table cell for each group (YEAR).

    Sums the sales for the current group (YEAR).

    Closes the for-each-group statement.

    Closes the for-each-group statement.

    Note that only the first row uses the @column context to determine the number of columns for the table. All remaining rows must use the @cell context to create the table cells for the column. See Controlling the Placement of Instructions Using the Context Commands.

    Constructing Dynamic Data Columns

    The ability to construct dynamic data columns is a very powerful feature of the RTF template. Using this feature you can design a template that correctly renders a table when the number of columns that is required by the data is variable.

    For example, you are designing a template to display columns of test scores within specific ranges. However, you do not how many ranges have data to report. You can define a dynamic data column to split into the correct number of columns at runtime.

    Use the following tags to accommodate the dynamic formatting required to render the data correctly:

    Dynamic Column Header

    group element name ?>

    Use this tag to define which group to split for the column headers of a table.

    Dynamic Column group element name ?>

    Use this tag to define which group to split for the column data of a table.

    Dynamic Column Width

    Use one of these tags to define the width of the column when the width is described in the XML data. The width can be described in two ways:

    An XML element stores the value of the width. In this case, use the syntax name ?> , where name is the XML element tag name that contains the value for the width.

    If the element defined in the split-column-header tag, contains a width attribute, use the syntax to use the value of that attribute.

    Dynamic Column Width’s unit value (in points) value ?>

    Use this tag to define a multiplier for the column width. If the column widths are defined in character cells, then you must use the appropriate multiplier value to render the columns to the correct width in points. For example, if you are using 10 point courier font in the table, you would use a multiplier of 6, which is the approximate width of a character displayed in 10 point courier font. If the multiplier is not defined, then the widths of the columns are calculated as a percentage of the total width of the table. The column width calculations are illustrated in the following table:

    This Excel VBA tutorial explains how to set print area, page width, Print Title, Orientation in Excel VBA.

    Excel VBA set print area

    To set Print Area in Excel spreadsheet, first select (highlight) Cells that we want to set as Print Area, and then navigate to ribbon Page Layout > Print Area > Set Print Area

    To set Print Area in Excel VBA, we have to use PageSetup.PrintArea Property.

    For example, in order to set Print Area A1:N21, we can write

    Excel VBA set page width and height

    After we have defined the Print Area, the next step is to set the page width and height.

    For most of the time, we want the page Width to be 1 page, while the height is Automatic, meaning we let Excel decide how many pages to print depending on how many rows are in the data, we just need to ensure all the field headers are displayed in one page.

    While the above code would work perfectly, in fact, it is more correct to set the FitToPagesTall = 0 to make it Automatic, but this command would generate an error

    Run-time error ‘1004’: Unable to set the FitToPagesTall property of the PageSetup class

    The error is generated when we have not turned on the communication to the printer. To avoid this error, we can temporarily turn off the communication using Application.PrintCommunication Property.

    Excel VBA set Print Title

    In case the number of records (rows) is so large that it takes more than 1 page to print all the data, we need to ensure all pages contain the field headers on top of each page. In Excel spreadsheet, we set it under Page Layout > Print Titles > Rows to repeat at top

    In VBA, we set this using PrintTitleRows Property.

    Excel VBA set Orientation

    Sometimes we want to rotate the paper by 90 degree to Landscape when there are too many fields to display in Portrait layout.

    In VBA, we can either set Orientation = xlPortrait or Orientation = xlLandscape

    Other Properties

    I have introduced the most commonly used properties in this article, in fact, there are more than 50 properties for PageSetup Object. If you want to learn other properties, click here.

    Hello Excellers and welcome to another Excel Tip in my #macromonday 2019 series. Today let’s look at how to copy cells based on specific criteria in your Excel worksheet. This is a macro I created to help speed up a repetitive process at work. I needed to copy and paste cells that were greater than zero. Specifically, all products that had a value greater than zero next to them. You can see a sample of similar data and my expected outcome from my Excel macro.

    Copy Cells Meeting Criteria With An Excel VBA Macro

    All of the rows with values in Column C have been copied to another area of my worksheet. Exactly what I need. So let’s get writing some VBA code right.

    Preparing To Write The Macro

    First, we will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. We then have a choice. Either create a module to store your code either in your Personal Macro Workbook or alternatively in your current workbook. What’s the difference?. If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks. So, if you store it in the current workbook then use is restricted to that workbook.

    Learn More About Your Personal Macro Workbook (PMW)

    If you want to read more about your Excel PMW then check out my blog posts below.

    Starting The Macro

    We need to start off the macro by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of the macro. In this example, I have called the macro CopyAboveZero. Excel automatically inserts the End Sub piece of code for us which ends the Sub Routine. So, all we need to do is insert the rest of the code.

    [stextbox CopyCells
    End Sub[/stextbox]

    Turn Off Screen Updating

    By turning off screen updating we avoid the screen flashing or flickering as the Excel executes the VBA code. Any time a change occurs on your Excel worksheet, the screen will update.

    [stextbox = False
    [/stextbox]

    Declaring Variables

    Next, we need to declare any variables we want to use in this macro. This just means that Excel allocates some memory to these values. We are also setting a range which represents the data in Columns E2 and F2 to the last row containing values.

    [stextbox i As Integer
    Dim Lastrow As Long
    ‘Declaring variables
    Dim rng As Range
    Set rng = Range(“E2:F2” & Lastrow)

    Finding The Last Row In Column A

    We now find the last row in Column A. Probably like hitting the Ctrl+ Down arrow. This way we setting the cells that contain any values that we want to copy across our worksheet.

    [stextbox = Cells(Rows.Count, “A”).End(xlUp).Row
    [/stextbox]

    Use The For Next Loop With IF Then

    The next stage is to use For…Next looping. Excel loops through to the last cell in Column B. It looks for cells which match our criteria which we have specified to be >= (greater than or equal to) 10. So, if these values are found then the code moves to the next stage of code.

    In this case, the next stage is to show the value in the matching row in Column A in Column E. I also want to equate the cell values of Column C To Column F.

    Excel continues to loop through all of the cells until all cells that meet the criteria have analyzed. Once all of the cells are completed the looping ends.

    [stextbox i = 1 To Lastrow
    If Cells(i, 1).Value >= 10 Then
    Cells(i, 5).Value = Cells(i, 2).Value
    Cells(i, 6).Value = Cells(i, 3).Value
    End If
    Next
    [/stextbox]

    Remove Blank Cells.

    I am now left with all of the correct data in Column E and F. My original data remains unchanged. The next stage is to remove the blank cells and move the data up to one complete new data set. By selecting the range set at the time we declared variables Excel will delete any blank cells and move our data to a neat complete data set.

    [stextbox > rng.Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp
    [/stextbox]

    Turn Screen Updating Back On.

    Next, we can turn screen updating back on after Excel has completed looping our code and deleting blank cells.

    [stextbox > Application.ScreenUpdating = True
    [/stextbox]

    Ending The Excel Macro.

    Finally, once all of the selected range has been checked for negative values the code finally ends. This instruction was already entered into the module for us when started the type the name of the macro.

    [stextbox > End Sub
    [/stextbox]

    How to repeat specific rows or columns on every printed page in excel

    If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

    Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below

    How To Excel At Excel – Macro Mondays Blog Posts.

    So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.