Define range between two page breaks excel VBA

0
votes

i have an excel sheet that is separated by multiple page breaks - for each change in column C (column C represents specific ProjecID).

Each line of the table contains certain amount that has been spent. This amount is stored in column Q. (Note - for each project there can be multiple rows - amounts in column Q).

Depending if the subtotal amount (column Q) per project (column C) is <0 or >0, different operations should be performed within that range.

Question - How to loop through these ranges (between two page breaks) in order to check subtotal amount and perform appropriate operations on rows within each range?

Thank you in advance

2
excelvba

2 Answers

1
votes

Following code deletes all previous page & then you can set up a Range, 

Sub PageBreak()     
Dim CellRange As Range   
Dim TestCell As Range

Set CellRange = Selection   
For Each TestCell In CellRange
    ActiveSheet.Rows(TestCell.Row).PageBreak = xlPageBreakNone       
    If TestCell.Value <> TestCell.Offset(-1, 0).Value Then
        ActiveSheet.Rows(TestCell.Row).PageBreak = xlPageBreakManual         
    End If     
Next TestCell 
End Sub

NB: Select the cells from where you want to splits, don't count the Col headings, like if the Country name are in column D from rows 2 through 30, you would select the range in D3 through D30.

0
votes

try this. It removes page breaks from all sheets. Hope it helps.

       Sub PageBreak()

         Dim ws As Worksheet, tws As Worksheet
            Set tws = ActiveSheet
            For Each ws In Worksheets
                ws.Activate
                ActiveWindow.View = xlPageBreakNone
               Next ws
                tws.Activate
        End Sub