将"范围"设置为"最后一行和打印区域末尾之间的空白"-Excel VBA打印区域

Question

我想在工作表的最后一行添加一个底部边框，用于注释。所以它会打印空行。我正试图弄清楚如何在该页面上将范围设置为LastRow+1到打印区域的末尾。

我找到了这个函数，但它似乎只是返回我输入的范围，我不确定它是否在做我想做的事情，有什么想法吗？

Sub Test()
ActiveSheet.PageSetup.PrintArea = "A1:F10000"
Call PrintAreaLastRow
End Sub
Function PrintAreaLastRow() As Long
With Range(ActiveSheet.PageSetup.PrintArea)
PrintAreaLastRow = .Rows(.Rows.Count).Row
Debug.Print PrintAreaLastRow
End With
End Function

Answer 1

https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.hpagebreaks

https://learn.microsoft.com/en-us/office/vba/api/excel.hpagebreaks

测试：

Sub Test()
Dim wb As Workbook, ws As Worksheet
Set wb = ActiveWorkbook
Set ws = ActiveSheet
Dim lPrintRow As Integer
lPrintRow = LastPrintRow(ws)
Debug.Print lPrintRow
Dim UnUsedRng As Range
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
lColLet = Split(Cells(1, lCol).Address, "$")(1)
lRow = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
Set UnUsedRng = ws.Range("A" & lRow & ":" & lColLet & lPrintRow)
UnUsedRng.Borders(xlBottom).LineStyle = xlContinuous
UnUsedRng.Borders(xlBottom).Weight = xlThin
End Sub

功能：

Public Function LastPrintRow(ByRef strSheet As Worksheet) As Integer
Dim RowsPerPage As Long, PrintPages As Long
pgBreak = strSheet.HPageBreaks(1).Location.Address(False, False)
RowsPerPage = Right(pgBreak, Len(pgBreak) - 1) - 1
PrintPages = strSheet.HPageBreaks.Count + 1
LastPrintRow = RowsPerPage * PrintPages - PrintPages + 1
Debug.Print LastPrintRow
End Function

Answer 2

如果我正确理解你的问题，这应该可以

Sub Test()
Dim rngBlankLines As Range
Dim ws As Worksheet: Set ws = ActiveSheet

ws.PageSetup.PrintArea = "A1:F10000"

With ws.Range(ws.PageSetup.PrintArea)
Set rngBlankLines = ws.Range(ws.Cells(GetPrintAreaLastUsedRow(ws) + 1, .Cells(1, 1).Column), _
.Cells(.Rows.Count, .Columns.Count))
End With

With rngBlankLines
' Select to check if it is the range you expect (Delete if correct)
.Select

'        ' Add your formatting below (uncomment if rngBlankLines is the correct range)
'        With .Borders(xlEdgeBottom)
'            .LineStyle = xlContinuous
'            .Weight = xlThin
'        End With
'
'        With .Borders(xlInsideHorizontal)
'            .LineStyle = xlContinuous
'            .Weight = xlThin
'        End With
'
'        ' Perhaps give more room for hand-writing
'        .EntireRow.RowHeight = .Cells(1, 1).Offset(-1, 0).EntireRow.RowHeight * 1.5
End With

End Sub
Function GetPrintAreaLastUsedRow(ws As Worksheet) As Long
Dim i As Long, lTemp As Long

If ws.PageSetup.PrintArea = "" Then Exit Function

With ws.Range(ws.PageSetup.PrintArea)
For i = 1 To .Columns.Count
lTemp = .Cells(.Rows.Count, i).End(xlUp).Row
If GetPrintAreaLastUsedRow < lTemp Then GetPrintAreaLastUsedRow = lTemp
Next i
End With
End Function