如何在VBA中使用Excel识别分页符 [英] How to identify page breaks in excel with VBA, varying conditions

问题描述

这是我一直在努力的问题.原谅我没有引用我研究过的确切线程，但我一直没有记录下来.我下面的代码示例是通过审阅有关此主题的许多线程而构建的.但是，我需要的确切解决方案仍使我回避.

This is a problem I've been wrestling with for some time. Forgive me for not citing the exact threads I've researched, but I've not been keeping a log. The code sample I have below has been constructed from reviewing many threads on this topic. However, the exact solution I need still evades me.

简而言之:在Excel VBA中，我需要能够在动态创建完长表后，在长表中识别出每页的最后一行和第一列/最后一列，该表的长度可能为1至5页(水平)并填充.我也希望能有一种快速的方法来确定填充的数据在哪里停止，但是如果没有简单的解决方案，可以从生成例程中获取.以下代码将识别单页表的正确的最后一行和最后一列...一次.我可以进行修改以检查多页表的后续页面.我遇到的真正问题是，一旦填充并清除了一个单元格，excel就会将该单元格包含在已用单元格范围内.此相同代码的后续执行失败，因为不再正确标识最后一个单元格.无论如何，有什么办法可以扭转这种局面，或者我可以采取其他方法吗?

Simply put: In Excel VBA, I need to be able to identify the last row and first/last column of each page in a long table which could be from 1 to 5 pages long (horizontally), after having been dynamically created and populated. I would also appreciate a quick way to identify where the populated data stops, but can get that from the generating routine if there's no easy solution. The following code will identify the correct final row and column of a single page table... once. I can make the modification needed to check subsequent pages for a multi-page table. The real problem I'm having is that once a cell has been populated and cleared, excel includes that cell in the range of used cells. Subsequent executions of this same code fail, because the last cell is no longer correctly identified. Is there anyway to reverse this, or a different approach I could take?

我确实不喜欢填充单元格并将其删除以查找分页符，但是我没有找到一种避免这样做的解决方案.感谢您提供的任何指导.，麦克·沙纳汉(Mike Shanahan)

I truly do not like populating cells and deleting them to find the page breaks, but I've not found a solution that avoids doing so. Thanks for any guidance you might provide. ,Mike Shanahan

Sub Findpagebreaks()

Dim x As HPageBreaks, pb_x As HPageBreak
Dim y As VPageBreaks, pb_y As VPageBreak, PageMatrix() As Integer
Dim LastPopulated(1) As Integer, test As Integer, target As Integer

ReDim PageMatrix(1 To 1, 0 To 2) As Integer
With ActiveSheet
    Debug.Print "============================================"

    Debug.Print "Horizontal Page Breaks"
    Set x = .HPageBreaks
    Debug.Print "Initial Hbreaks: ", x.Count
    LastPopulated(0) = .Cells.SpecialCells(xlCellTypeLastCell).Row
    Debug.Print "Last row of data: ", LastPopulated(0)
    target = x.Count + 1
    test = LastPopulated(0)
    Do While x.Count < target
        test = test + 10
        .Cells(test, 1).Value = "."
        Debug.Print "cell: " & .Cells(test, 1).Address & " populated"
        If test > 100 Then Exit Do
    Loop
    For Each pb_x In x
        If pb_x.Extent = xlPageBreakFull Then
            Debug.Print "Row: " & pb_x.Location.Row, "Full Page Break"
            PageMatrix(1, 2) = pb_x.Location.Row - 1
        Else
            Debug.Print "Row: " & pb_x.Location.Row, "Partial Page Break"
        End If
    Next pb_x
    .Range(.Cells(LastPopulated(0) + 1, 1), .Cells(test, 1)).ClearContents
    Debug.Print "cells: " & .Range(.Cells(LastPopulated(0), 1), .Cells(test, 1)).Address & " cleared."
    Debug.Print "Horizontal Exploration complete."


    Debug.Print "Vertical Page Breaks"
    Set y = .VPageBreaks
    Debug.Print "Initial vbreaks: ", y.Count
    LastPopulated(1) = .Cells.SpecialCells(xlCellTypeLastCell).Column
    Debug.Print "Last column of data: ", LastPopulated(1)
    target = y.Count + 1
    test = LastPopulated(1)
    Do While y.Count < target
        test = test + 10
        .Cells(1, test).Value = "."
        Debug.Print "cell: " & .Cells(1, test).Address & " populated"
        If test > 100 Then Exit Do
    Loop
    PageMatrix(1, 0) = 1
    For Each pb_y In y
        If pb_y.Extent = xlPageBreakFull Then
            Debug.Print "column: " & pb_y.Location.Column, "Full Page Break"
            PageMatrix(1, 1) = pb_y.Location.Column - 1
        Else
            Debug.Print "Row: " & pb_y.Location.Column, "Partial Page Break"
        End If
    Next pb_y
    .Range(.Cells(1, LastPopulated(1) + 1), .Cells(1, test)).ClearContents
        Debug.Print "cells: " & .Range(.Cells(1, LastPopulated(1)), .Cells(1, test)).Address & " cleared."
    Debug.Print "Vertical Exploration complete."


    Debug.Print "Page", "First Col", "Last Col", "Last Row"
    Debug.Print 1, PageMatrix(1, 0), PageMatrix(1, 1), PageMatrix(1, 2)
    Debug.Print "~~~~~~~~~~~~~~~~~~~~~~"
    Debug.Print "Sub complete."
    Debug.Print

End With
End Sub

推荐答案

因此，我选择了一种解决方案，该解决方案可以减少编码并提供更可靠的产品.这不是我一直在寻找的复杂答案，仍然属于蛮力方法论，但这绝对减轻了负担.

So I've opted for a solution that reduces the coding and makes a more robust product. It isn't the sophisticated answer I was looking for, still falls under brute force methodology, but it's definitely less burdensome.

这是我目前最好的.如果有人找到了更优雅的解决方案，请随时发布！谢谢大家

This is the best I have for now. IF someone finds a more elegant solution, feel free to post it! Thanks all.

Sub Findpagebreaks()

Dim y As VPageBreaks, pb_y As VPageBreak, PageMatrix() As Integer
Dim LastPopulated(1) As Integer, roww As Integer, coll As Integer
Dim lastpage As Integer, LookingForLastPage As Boolean, last_row As Integer

ReDim PageMatrix(0 To 2, 1 To 1) As Integer
PageMatrix(0, 1) = 1   'First column of page 1, always = 1
LookingForLastPage = True
lastpage = 1

With ActiveSheet
    '  In practice, this sub will be passed values for row and coll
    '  which represent the anticipated row & col that are populated
    '  to span the used range.
    roww = 13
    coll = 1
    LastPopulated(0) = .Cells(.Rows.Count, coll).End(xlUp).Row
    LastPopulated(1) = .Cells(roww, .Columns.Count).End(xlToLeft).Column
    Debug.Print "Last row of data: ", LastPopulated(0)
    Debug.Print "Last column of data: ", LastPopulated(1)
    .Cells(LastPopulated(0) + 50, LastPopulated(1) + 10).Value = "."
    last_row = .HPageBreaks(1).Location.Row - 1
    Set y = .VPageBreaks
    For Each pb_y In y
        If LookingForLastPage Then
            If lastpage > 1 Then
                ReDim Preserve PageMatrix(0 To 2, 1 To lastpage) As Integer
                PageMatrix(0, lastpage) = PageMatrix(1, lastpage - 1) + 1
            End If
            PageMatrix(1, lastpage) = pb_y.Location.Column - 1
            PageMatrix(2, lastpage) = last_row
            If pb_y.Location.Column > LastPopulated(1) Then _
                LookingForLastPage = False Else: lastpage = lastpage + 1
        End If
    Next pb_y
    .Cells(LastPopulated(0) + 50, LastPopulated(1) + 10).ClearContents
    Debug.Print "Page", "First Col", "Last Col", "Last Row"
    For i = 1 To lastpage
        Debug.Print i, PageMatrix(0, i), PageMatrix(1, i), PageMatrix(2, i)
    Next i
    Debug.Print "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
    Debug.Print "Last page is: ", lastpage

End With
End Sub

这篇关于如何在VBA中使用Excel识别分页符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！