Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Page Numbers in VBA.

# Page Numbers in VBA

Written by Allen Wyatt (last updated August 16, 2021)
This tip applies to Excel 2007, 2010, 2013, and 2016

Steve is looking for a way to determine, in a VBA macro, the number of pages that a worksheet will have, when printed, and the page number on which a particular cell will print. This task is not quite as easy as one would hope, but it can be done.

It seems that the best way to handle this is to use an outmoded (but still available) Excel 4 function to determine the number of total printed pages in a worksheet. Then you can use the HPageBreaks and VPageBreaks collections to figure out where the cell falls in the matrix of pages that will be printed. The following is an example of a macro that utilizes these items:

```Sub PageInfo()
Dim iPages As Integer
Dim iCol As Integer
Dim iCols As Integer
Dim lRows As Long
Dim lRow As Long
Dim x As Long
Dim y As Long
Dim iPage As Integer

iPages = ExecuteExcel4Macro("Get.Document(50)")

With ActiveSheet
y = ActiveCell.Column
iCols = .VPageBreaks.Count
x = 0
Do
x = x + 1
Loop Until x = iCols _
Or y < .VPageBreaks(x).Location.Column
iCol = x
If y >= .VPageBreaks(x).Location.Column Then
iCol = iCol + 1
End If

y = ActiveCell.Row
lRows = .HPageBreaks.Count
x = 0
Do
x = x + 1
Loop Until x = lRows _
Or y < .HPageBreaks(x).Location.Row
lRow = x
If y >= .HPageBreaks(x).Location.Row Then
lRow = lRow + 1
End If

If .PageSetup.Order = xlDownThenOver Then
iPage = (iCol - 1) * (lRows + 1) + lRow
Else
iPage = (lRow - 1) * (iCols + 1) + iCol
End If
End With
MsgBox "Cell " & ActiveCell.Address & _
" is on " & vbCrLf & "Page " & _
iPage & " of " & iPages & " pages"
End Sub
```

One thing that you should keep in mind with this macro is that the HPageBreaks and VPageBreaks collections are only considered accurate if you are viewing the worksheet in Page Break Preview (View | Page Break Preview). Thus, you'll want to make sure that you are in that mode before selecting a cell and running the macro.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11581) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Page Numbers in VBA.

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

Counting Characters in a Selection with VBA

Need to figure out the number of characters in a range of selected text? Here's how to do it in VBA.

Discover More

Comparing Document Versions

Do you need to compare two versions of a document to each other? Word provides a tool that can make this easy, as ...

Discover More

Problems Pasting Large Pictures

If you insert a large picture in your document and your text jumps all around and the picture seems to disappear, don't ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

##### More ExcelTips (ribbon)

Unprotecting Groups of Worksheets

Unprotecting a single worksheet is relatively easy. Unprotecting a whole lot of worksheets is harder. Here's how you can ...

Discover More

Disabling All Function Keys Except One

Disabling function keys is rather easy to do when you rely on the OnKey method in a macro. This tip looks at how you can ...

Discover More

Understanding the Personal Workbook Filename

The personal macro workbook is a great place to save your macros that you want to use with any workbook. The personal ...

Discover More
##### Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. Youâ€™ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is five minus 5?

There are currently no comments for this tip. (Be the first to leave your commentâ€”just use the simple form above!)

##### This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.