Count the number of pages (except pages with no data)

0

excel file, Sheet1 has 17 pages in total. Except for page 3, the remaining pages have data. How do I use the vba code to find the result of the page number containing 16 pages of data. Thank you all. Wish one good day

Answer
Discuss

Answers

0

Please try this code on your worksheet. It extracts most of the information you might need.

Sub BlankPages()
    ' 121

    Dim Cl      As Long             ' last used column
    Dim Rstart  As Long             ' start row of a new page
    Dim Rng     As Range            ' range of one page
    Dim Page    As Integer          ' page counter
    Dim Blanks  As Integer          ' blank page counter
    Dim IsBlank As Integer          ' test for being blank
    Dim R       As Long             ' loop counter: rows

    Rstart = 1
    With Worksheets("Sheet1")       ' change name to suit
        Cl = .UsedRange.Columns.Count
        For R = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
            If .Rows(R).PageBreak <> xlPageBreakNone Then
                Set Rng = .Range(.Cells(Rstart, 1), .Cells(R - 1, Cl))
                IsBlank = 1 - Sgn(WorksheetFunction.CountA(Rng))
                Page = Page + 1
                If IsBlank Then Blanks = Blanks + 1
                Debug.Print "Page " & Page & " range = " & Rng.Address(0, 0) & Chr(9) & _
                            "It's" & IIf(IsBlank, "", " not") & " blank."
                Rstart = R
            End If
        Next R
        MsgBox "There are " & Page & " pages in worksheet " & _
               Chr(34) & .Name & """." & vbCr & _
               Blanks & " of them " & IIf(Blanks = 1, "is", "are") & " blank.", _
               vbInformation, "Page count"
    End With
End Sub

Here is what it does not do. It doesn't differentiate between page breaks inserted manually and those that are created by Excel automatically. It also doesn't account for page breaks of either type that might be inserted by vertical splits.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login