VBA to add page breaks on varible data

[DeanRobinson]

Hi all, id like to find a way of adding a page break each time a cell value changes but not taking into account blank cells. so for example

AW
AW
AW
SPACE
AW
AW
AW
SPACE
PAGE BREAK
GS
GS
GS
GS
SPACE

So the page break is because the cell has changed from AW to GS, but there were spaces in AW's range that i dont want to take into account if that makes sense.

 

[ChrisOswald]

Hi,
Try this.

Sub AddPageBreaksAtChanges()
    Dim i As Long
    Dim lr As Long
    Dim strLastVal As String
    lr = Range("A" & Rows.Count).End(xlUp).Row
    'change the second 1 in the cells() references to whatever
    'column your values are in
    For i = 1 To lr
        If Trim(Cells(i, 1)) <> "" Then
            If strLastVal <> "" Then
                If Cells(i, 1) <> strLastVal Then
                    strLastVal = Cells(i, 1)
                    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(i, 1)
                End If
            Else
                strLastVal = Cells(i, 1)
            End If
        End If
    Next i
End Sub