Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • 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 SalvucciSystems 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 SalvucciSystems ManagerAuthor Commented:
Thank you for the quick answer! Much appreciated!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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