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
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Count the number of pages (except pages with no data)
0
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.