Solved

Page Break via VBA

Posted on 2011-03-22
5
382 Views
Last Modified: 2012-05-11
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
Comment
Question by:Lawrence Salvucci
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35188756
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
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35188813
It's faster to use excel's search within vba to search up for "OV".
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 35188867
Can you explain what you mean about Excel's search within VBA?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35188874
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
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
ID: 35188928
Thank you for the quick answer! Much appreciated!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question