0

I have an Excel file created from a SQL Report with a massive amount of tables.

I have to either delete rows or add page breaks depending on a cell's value.

Here is a Google Spreadsheet that shows a very basic version of my Excel file. https://docs.google.com/spreadsheets/d/1cvDuVpu3NQqMf7wkj8D3DB1y54TmczaSbTZbx_26sd8/edit?usp=sharing

If a set's Total is not 0, a page break is added. If a set's Total is 0, delete the set. The total is known by if its cell having a left border.

The problem with my code is: if a delete of a set occurs, and the next set has a lower row count than the deleted set, the code misses that set and starts affecting the following set.

In the example supplied, the FIRST set (total <> 0) and SECOND set (<> 0) each get page breaks. The THIRD set (= 0) is deleted. Since the FOURTH set is smaller in row count than the THIRD, the code appears in the FIFTH set, which is deleted (= 0) and deletes the FOURTH set with it. Had the FIFTH set not = 0, it'd leave the FOURTH set untouched and add the page break to the FIFTH set.

Here's the code:

Sub Hello()
    For Each myWorksheet In Worksheets
        myWorksheet.Activate
        With myWorksheet
            Dim Lastrow As Integer
            Lastrow = myWorksheet.UsedRange.Rows.Count
            Dim intLastStartRow As Integer
            intLastStartRow = 12
            For Each myCell In Range("B12:B" & Lastrow).Cells
                Dim lastRange As Range
                If (myCell.Borders(xlEdgeLeft).LineStyle <> xlNone) Then
                    Dim borderRange As Range
                    Set borderRange = myWorksheet.Range("B" & myCell.Row)
                    If (borderRange.Value <> 0) Then
                        myWorksheet.Rows(myCell.Row + 1).PageBreak = xlPageBreakManual
                        intLastStartRow = myCell.Row + 1
                    Else
                        myWorksheet.Rows(intLastStartRow & ":" & myCell.Row).Delete
                    End If
                End If
            Next
        End With
    Next
End Sub
Community
  • 1
  • 1
Matt Hansen
  • 424
  • 1
  • 6
  • 16

1 Answers1

1

I'm not 100% sure I understand the problem and I have never tried deleting rows with a For Each cell In range loop, but I suspect it fails the same way as a regular For-Loop. Excel gets messy when deleting cells in forward order; it works much better in reverse order.

The trick here might be to use a loop structured like this : 

last = myWorksheet.Cells(myWorksheet.Rows.Count, 2).End(xlUp).Row
For x = last to 12 Step -1
    'Do Stuff
Next x
Bernard Saucier
  • 2,240
  • 1
  • 19
  • 28