replace defined text with page break in vba + excel
 

I have a text file imported into excel.
I have a varying number of cells in column A containing text ="xxx" that I
would like to replace with a page break when running a macro

replace defined text with page break in vba + excel
 

If you haven't received an answer from somewhere else, perhaps this will
help. It will remove any existing page breaks on the selected sheet and then
insert new ones at the rows containing the specified text in column A of that
row.

Sub AddPageBreaks()
Dim LC As Long ' a loop counter
'Delete any existing page breaks on the sheet
For LC = ActiveSheet.HPageBreaks.Count To 1 Step -1
ActiveSheet.HPageBreaks(LC).Delete
Next
'now add page break above any cell with text "a1" in it
For LC = 1 To Range("A" & Rows.Count).End(xlUp).Row
If Range("A1").Offset(LC, 0) = "a1" Then
ActiveSheet.HPageBreaks.Add Befo=Range("A1").Offset(LC, 0)
End If
Next
End Sub

If you need the text to be at the bottom of the page instead of at the top
of the next, then change the code to read:
ActiveSheet.HPageBreaks.Add Befo=Range("A1").Offset(LC+1, 0)


"BillyH" wrote:

I have a text file imported into excel.
I have a varying number of cells in column A containing text ="xxx" that I
would like to replace with a page break when running a macro