Page Break via VBA

I need to add a page break in my VBA code.

I need to insert it when it finds the last value of column E that begins with OV. Once it finds the last row with that beginning value then I need to add a page break. How can I do this?
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SiddharthRoutConnect With a Mentor Commented:
Try this. Do remember to replace "Sheet1" with the respective sheet name.

Sub Sample()
    Dim i As Long, LastRow As Long
    
    LastRow = Sheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Row
    
    For i = LastRow To 1 Step -1
        If UCase(Left(Sheets("Sheet1").Range("E" & i).Value, 2)) = "OV" Then
            Sheets("Sheet1").Range("E" & i + 1).Select
            ActiveWindow.SelectedSheets.HPageBreaks.Add before:=ActiveCell
            ActiveWindow.SelectedSheets.VPageBreaks.Add before:=ActiveCell
            Exit Sub
        End If
    Next i
End Sub

Open in new window


Sid
0
 
CluskittCommented:
It's faster to use excel's search within vba to search up for "OV".
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Can you explain what you mean about Excel's search within VBA?
0
 
SiddharthRoutCommented:
Yes you are right and I have been a biggest advocate of .Find(). However when you need to find just one last value in the column then the above loop will also do :)

Sid
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you for the quick answer! Much appreciated!
0
All Courses

From novice to tech pro — start learning today.