If a cell in column B contains the word "Total" delete or hide the blank row above

[Jasz74]

Hi
I've tried searching for this but nothing quite matches (can only seem to find if the cell equals something delete that row the cell is in).

Can this be done? Can someone help me with the vba coding please?

Context - I have created a button that generates a formatted report based on pivot table data. I want it to subtotal cube and value for each container and if there are multiple orders in that container I want them separated by a blank row which is all fine, but then it puts this extra blank row between the last value for each container and the subtotal for each container and I don't want that blank line to show. Pivot table field settings either do both or none.

I've attached a screenshot of the report - the highlighted line is the one I don't want to show.

 

[Michael M]

Maybe this way

Sub MM1()
Dim r As Long
For r = 2 To Cells(Rows.Count, "B").End(xlUp).Row
    If Cells(r, 2) = "Total" Then Rows(r - 1).Delete
Next r
End Sub

 

[bebo021999]

Sub Del()
Dim lr&, i&, j&, k&, c&, rng, arr(1 To 1000000, 1 To 1)
lr = Cells(Rows.Count, "B").End(xlUp).Row
rng = Range("B1:H" & lr).Value
On Error Resume Next
For i = 2 To lr
    If rng(i, 1) <> "" Then
       If UCase(rng(i, 1)) Like "*TOTAL" Then
            c = 0
            For j = 1 To 7
                c = c + Len(rng(i - 1, j))
            Next
            If c = 0 Then
                k = k + 1
                arr(k, 1) = "B" & i - 1
            End If
        End If
    End If
Next
For i = 1 To k
    Range(arr(i, 1)).Value = "#N/A"
Next
Columns("B").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
End Sub

 

[Jasz74]

Maybe this way

Sub MM1()
Dim r As Long
For r = 2 To Cells(Rows.Count, "B").End(xlUp).Row
    If Cells(r, 2) = "Total" Then Rows(r - 1).Delete
Next r
End Sub

Almost - I had to tweak it slightly with a bit of bebo021999's code works exactly as I need it to! Thanks

Dim r As Long
For r = 2 To sheets("Report").Range("B" & Rows.Count).End(xlUp).Row
If Sheets("Report").Cells(r, 2) Like "*Total" Then Sheets("Report").Rows(r - 1).EntireRow.Delete
Next r

 

[bebo021999]

Almost - I had to tweak it slightly with a bit of bebo021999's code works exactly as I need it to! Thanks

Dim r As Long
For r = 2 To sheets("Report").Range("B" & Rows.Count).End(xlUp).Row
If Sheets("Report").Cells(r, 2) Like "*Total" Then Sheets("Report").Rows(r - 1).EntireRow.Delete
Next r

Although it seems it is working, but take into account:
1) Avoid loop, calculate, read and write, delete, insert... directly on worksheet. It may slow down the code, if data was big enough (few thousands of rows)
2) When looping rows then delete rows, it should be looping from bottom:
For r = (last row) to 2 step -1
<then detele row>
...
Next
Otherwhile, some rows may be missed.