• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

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?
0
Lawrence Salvucci
Asked:
Lawrence Salvucci
  • 2
  • 2
1 Solution
 
SiddharthRoutCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now